Member Avatar for Seba Sama

Hi
can anyone help me on how to get the names of the tables from a DB - MSSQL 2k server? i need to list them or add them into a combobox, but i also need to select them by owner, or by type. user defned or system.
thx in advance

  • 2 Contributors
  • 2 Replies
  • 483 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by Seba Sama

Dani AI

Generated

Good start by — INFORMATION_SCHEMA is fine. Below are two practical, reliable ways to list tables in SQL Server 2000 and to filter by owner or type, plus a brief WinForms combobox note and common pitfalls to avoid.

SELECT TABLE_SCHEMA AS Owner,
       TABLE_NAME   AS Name,
       TABLE_TYPE   AS Type
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'   -- use 'VIEW' for views
  AND TABLE_SCHEMA = 'dbo'        -- filter by owner
ORDER BY Name;

In SQL Server 2000 the INFORMATION_SCHEMA TABLE_SCHEMA column contains the table owner (schemas were introduced in 2005). TABLE_TYPE returns values like BASE TABLE or VIEW, so you can filter on that without calling object property functions.

SELECT Owner     = USER_NAME(o.uid),
       TableName = o.name,
       Type      = CASE o.xtype
                     WHEN 'U' THEN 'USER TABLE'
                     WHEN 'S' THEN 'SYSTEM TABLE'
                     WHEN 'V' THEN 'VIEW'
                     ELSE o.xtype
                   END
FROM sysobjects o
WHERE o.xtype IN ('U','S','V')   -- include or exclude system tables as needed
ORDER BY Owner, TableName;

Use sysobjects in 2k when you want the low-level xtype codes (U = user table, S = system table, V = view). To populate a WinForms combobox after running either query:

/* assume DataTable dt = result of query */
comboBox.DataSource = dt;
comboBox.DisplayMember = "TableName";
comboBox.ValueMember   = "Owner";   // or build Owner + '.' + TableName as the value

Tips and traps: run USE YourDatabase first; ensure the login has permission to read metadata; avoid calling OBJECT_ID with an unqualified name (schema/owner must be included); use QUOTENAME() when building identifiers to avoid malformed names or SQL injection. If you need to support SQL Server 2005+, prefer sys.objects, sys.tables and sys.schemas there.

Recommended Answers

All 2 Replies

Member Avatar for hollystyles

Use INFORMATION_SCHEMA. Example:

SELECT TABLE_SCHEMA,TABLE_NAME, OBJECTPROPERTY(object_id(TABLE_NAME), N'IsUserTable') AS type 
 FROM INFORMATION_SCHEMA.TABLES

There are many others, search the index in Books On-lline (the manual you get with SQl Server) for all the others or search the SQLTeam.com forums where there are many posts and articles on getting metadata from MS SQL Server.

Member Avatar for Seba Sama

thx, it helped me a lot.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.