Learn more about SQL Server tools

   
   




































Latest from MSSQLTips














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
Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources


print tip Print  
Become a paid author


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     





 
More SQL Server Solutions



 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.