Monday, 4 January 2010

SQL Server System Objects in User Defined Databases

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
Posted by hemora at 11:27

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)