Statements to Display Sql Server Objects

Followings are the statements to display various objects of SQL Server.

--Data Models

--SQL SERVER 2000

SELECT * FROM dbo.dtproperties
GO

-- SQL SERVER 2005
SELECT * FROM dbo.sysdiagrams;
GO

--Tables

-- SQL SERVER 2000

SELECT * FROM dbo.sysobjects WHERE xtype = 'u' ORDER BY Name
GO

-- SQL SERVER 2005

SELECT * FROM sys.tables ORDER BY Name
GO

--Columns

-- SQL SERVER 2000

SELECT o.name, c.name FROM dbo.syscolumns c INNER JOIN dbo.sysobjects o ON c.id = o.id WHERE o.name = 'MyTableName' ORDER BY c.colorder
GO

-- SQL SERVER 2005

SELECT OBJECT_NAME([Object_ID]) AS 'TableName', [Name] AS 'ColumnName', Column_ID FROM sys.all_columns ORDER BY TableName, Column_ID
GO

-- Primary Keys

-- SQL SERVER 2000

SELECT p.name, OBJECT_NAME(parent_obj) AS 'Table Name' FROM dbo.sysobjects p WHERE p.xtype = 'PK' ORDER BY p.Name
GO

-- SQL SERVER 2005

SELECT OBJECT_NAME(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey' FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.Type = 'PK' ORDER BY o.Name
GO

-- Foreign Keys

-- SQL SERVER 2000

SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS 'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.[name] AS 'PKColumnName' FROM sysforeignkeys f INNER JOIN syscolumns c1 ON f.fkeyid = c1.[id] AND f.fkey = c1.colid INNER JOIN syscolumns c2 ON f.rkeyid = c2.[id] AND f.rkey = c2.colid ORDER BY OBJECT_NAME(f.rkeyid)
GO

-- SQL SERVER 2005

SELECT OBJECT_NAME(f.constraint_object_id) AS 'ForeignKey', OBJECT_NAME(f.parent_object_id) AS 'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.referenced_object_id) AS 'PKTable', c2.[name] AS 'PKColumnName' FROM sys.foreign_key_columns f INNER JOIN sys.all_columns c1 ON f.parent_object_id = c1.[object_id] AND f.parent_column_id = c1.column_id INNER JOIN sys.all_columns c2 ON f.referenced_object_id = c2.[object_id] AND f.referenced_column_id = c2.column_id ORDER BY OBJECT_NAME(f.referenced_object_id)
GO

-- Constraints

-- SQL SERVER 2000

SELECT o.[name] AS 'DefaultName', OBJECT_NAME(c.[id]) AS 'TableName', col.[name] AS 'ColumnName' FROM dbo.sysconstraints c INNER JOIN dbo.sysobjects o ON c.constid = o.[id] INNER JOIN dbo.syscolumns col ON col.[id] = c.colid ORDER BY o.[name]
GO

-- SQL SERVER 2005

SELECT OBJECT_NAME(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey' FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.Type IN ('C', 'D', 'UQ') ORDER BY o.Name
GO

-- FileGroups\Partitions

-- SQL SERVER 2000

SELECT * FROM sysfilegroups
GO

-- SQL SERVER 2005

SELECT * FROM sys.data_spaces;
GO

-- Stored Procedures

-- SQL SERVER 2000

SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype = 'p' ORDER BY o.[Name]
GO

-- SQL SERVER 2005

SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] = 'p' ORDER BY o.[Name]
GO

-- Functions

-- SQL SERVER 2000

SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype IN ('fn', 'if', 'tf') ORDER BY o.[Name]
GO

-- SQL SERVER 2005

SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] IN ('fn', 'fs', 'ft', 'if', 'tf')ORDER BY o.[Name]
GO

-- Views

--SQL SERVER 2000

SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype = 'v' ORDER BY o.[Name]
GO

--SQL SERVER 2005

SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] = 'V' ORDER BY o.[Name]
GO