You are here: Home > Technical Articles > MS SQL Server - List All Sys Objects
Example on how to retrieve a list of all sys objects in MS SQL Server including tables, stored procedures, triggers and functions.
SELECT name,
CASE xtype WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint '
WHEN 'L' THEN 'Log '
WHEN 'P' THEN 'Stored procedure '
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K) '
WHEN 'RF' THEN 'Replication filter stored procedure '
WHEN 'S' THEN 'System tables '
WHEN 'TR' THEN 'Triggers '
WHEN 'U' THEN 'User table '
WHEN 'UQ' THEN 'UNIQUE constraint (type is K) '
WHEN 'V' THEN 'Views '
WHEN 'X' THEN 'Extended stored procedure '
WHEN 'TF' THEN 'Functions'
END AS type
FROM dbo.sysobjects
ORDER BY type ASC
Published On: 04 Jul 2009
MS SQL Server - List All Sys Objects
Example on how to retrieve a list of all sys objects in MS SQL Server including tables, stored procedures, triggers and functions.
SELECT name,
CASE xtype WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint '
WHEN 'L' THEN 'Log '
WHEN 'P' THEN 'Stored procedure '
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K) '
WHEN 'RF' THEN 'Replication filter stored procedure '
WHEN 'S' THEN 'System tables '
WHEN 'TR' THEN 'Triggers '
WHEN 'U' THEN 'User table '
WHEN 'UQ' THEN 'UNIQUE constraint (type is K) '
WHEN 'V' THEN 'Views '
WHEN 'X' THEN 'Extended stored procedure '
WHEN 'TF' THEN 'Functions'
END AS type
FROM dbo.sysobjects
ORDER BY type ASC
Published On: 04 Jul 2009
