TSQL - Non-system tables in an SQL Server 2000 database

Question

How to retrieve the names of Non-system -tables in a database from SQL Server 2000 by a T-SQL query?

Answer 1

You can use the sysobjects table

-- FOR TABLES
SELECT * 
FROM sysobjects o
WHERE type = 'U'

-- FOR VIEWS
SELECT * 
FROM sysobjects o
WHERE type = 'V'

-- FOR STORED PROCEDURES
SELECT * 
FROM sysobjects o
WHERE type = 'P'

You can also use INFORMATION_SCHEMA.TABLES in SQL Server 2000 onwards

SELECT * 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE'

Answer 2

select * from sysobjects
where xtype = 'U'

The system tables have an object_type of "S" - so that should give you just your user tables.

See the sysobjects documentation on MSDN for details on the possible object types.

Object type. Can be one of these object types:

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

Marc