January 5, 2009

Meta Data Queries in SQL Server 2000

-- Query to Find All User Tables

SELECT * FROM SYSOBJECTS SOWHERE SO.XTYPE = 'U' ORDER BY SO.NAME

-- Query to Find All User Views

SELECT SO.NAME FROM SYSOBJECTS SOWHERE SO.XTYPE = 'V' ORDER BY SO.NAME

--Query to Find all Procedures in the DB

SELECT SO.NAME FROM SYSOBJECTS SOWHERE SO.XTYPE = 'P' ORDER BY SO.NAME

--Query to Find all Triggers in the DB

SELECT SO.NAME FROM SYSOBJECTS SOWHERE SO.XTYPE = 'TR' ORDER BY SO.NAME

--Query to Find all User Defined Table Functions in the DB

SELECT SO.NAME FROM SYSOBJECTS SOWHERE SO.XTYPE = 'TF' ORDER BY SO.NAME

--Query to Find all User Defined Inline Functions in the DB

SELECT SO.NAME FROM SYSOBJECTS SOWHERE SO.XTYPE = 'IF' ORDER BY SO.NAME

--Query to Find all User Defined Scalar Functions in the DB

SELECT SO.NAME FROM SYSOBJECTS SOWHERE SO.XTYPE = 'FN' ORDER BY SO.NAME

Note.
X.Type define the object type, the possible values are
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