Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

SQL Server System Objects in User Defined Databases

MSSQLTips author Jeremy Kadlec By:   |   Read Comments   |   Related Tips: More > Database Administration

Problem
With the recent tips on data modeling (SQL Server 2005 Exposed = Data Modeling Tools and SQL Server Data Modeling Tools), a natural question that arose is: where are all of the underlying objects stored in SQL Server?  As such, in this tip we will outline where the user defined objects in user defined databases for both SQL Server 2000 and 2005.

Solution
Below outlines the queries for the objects in the user databases in both SQL Server 2000 and 2005:

ID Object Type SQL Server 2000 SQL Server 2005
1 Data Models Table = dtproperties

SELECT *
FROM dbo.dtproperties
GO

Table = dbo.sysdiagrams

SELECT *
FROM dbo.sysdiagrams;
GO

2 Tables Table = sysobjects

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

Table = sys.tables

SELECT *
FROM sys.tables
ORDER BY Name;
GO

3 Columns Table = syscolumns

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

Table = sys.all_columns

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

4 Primary Keys Table = sysobjects

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

Table = sys.objects

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

5 Foreign Keys Table = sysforeignkeys

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

Table = sys.foreign_key_columns

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

6 Constraints Table = sysconstraints

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

Table = sys.objects

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

7 FileGroups\Partitions Table = sysfilegroups

SELECT *
FROM sysfilegroups
GO

Table = sys.data_spaces

SELECT *
FROM sys.data_spaces;
GO

8 Stored Procedures Table = sysobjects

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

Table =  sys.objects

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

9 Functions Table = sysobjects

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

Table = sys.objects

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

10 Views Table = sysobjects

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

Table = sys.objects

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

Next Steps



Last Update: 11/3/2006


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
Sponsor Information