SQL Server System Objects in User Defined Databases


By:   |   Updated: 2006-11-03   |   Comments   |   Related: 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 Updated: 2006-11-03


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

How to read the SQL Server Database Transaction Log

How to rename a SQL Server database

Different ways to determine free space for SQL Server databases and database files

How to determine SQL Server database transaction log usage

Renaming Physical Database File Names for a SQL Server Database





get free sql tips
agree to terms


Learn more about SQL Server tools