SQL System Tables

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

 

Details

One thing to remember is that sysobjects, syscolumns and systypes are your friends. This are the database objects that we would be using for this demo.

Sysobjects contains information about each object in the database. this includes the object name, the user id of the user who created this object and many other useful information.

      select * from sysobjects;

The most useful column in this table is the xtype column. This column signifies what type of object is in the returned rowset. specifying

      select * from sysobjects where xtype = ‘u’ ;

will return the list of tables on the current database. Here’s a list of possible values for xtype:

  • C : CHECK constraint
  • D : Default or DEFAULT constraint
  • F : FOREIGN KEY constraint
  • L : Log
  • P : Stored procedure
  • PK : PRIMARY KEY constraint (type is K)
  • RF : Replication filter stored procedure
  • S : System tables
  • TR : Triggers
  • U : User table
  • UQ : UNIQUE constraint (type is K)
  • V : Views
  • X : Extended stored procedure
  • TF : Functions

You can use syscolumns  to retrieve columns on the database. doing

      select * from syscolumns;

would return a result containing column information which you can use to determine the scale, data type, precision and etc. The xtype on the syscolumns table acts differently from the sysobjects. the xtype column here represents the datatatype of that column. running this script:

   select * from syscolumns where xtype = 167;

will return all columns that has a datatype of varchar. heres a list of possible values for this xtype column:

127 : bigint
173 :  binary
104 :  bit
175 :  char
61 :  datetime
106 :  decimal
62 :  float
34 :  image
56 :  int
60 :  money
239 :  nchar
99 :  ntext
108 :  numeric
231 :  nvarchar
59 :  real
58 :  smalldatetime
52 :  smallint
122 :  smallmoney
98 :  sql_variant
231 :  sysname
35 :  text
189 :  timestamp
48 :  tinyint
36 :  uniqueidentifier
165 :  varbinary
167 :  varchar

I got this xtypes by running : select xtype, name from systypes; which basically contains a list of available sql datatypes.

Putting it all together: So my idea behind this article was to create a list of available tables, and views with their underlying columns. The idea is that i can just join the sysobjects to the syscolumns to get the the tables columns and the link it to systypes to get the column datatype. The complete scripts are listed below as reference to how i was able to accomplish this task.

—————————————–
— List only the tables in this database
—————————————–
SELECT    
 o.name AS [Table Name],
 o.type,
 c.name AS [Col Name],
 s.name AS [Col Type],
 c.prec,
 c.scale,
 c.isnullable
FROM       
 dbo.sysobjects AS o
INNER JOIN
 dbo.syscolumns AS c
  ON
   c.id = o.id
INNER JOIN
 dbo.systypes AS s
  ON
   c.xtype = s.xtype
WHERE  (
   o.type = ‘U’
)

—————————————–
— List only the views in this database
—————————————–
SELECT    
 o.name AS [View Name],
 o.type,
 c.name AS [Col Name],
 s.name AS [Col Type],
 c.prec,
 c.scale,
 c.isnullable
FROM       
 dbo.sysobjects AS o
INNER JOIN
 dbo.syscolumns AS c
  ON
   c.id = o.id
INNER JOIN
 dbo.systypes AS s
  ON
   c.xtype = s.xtype
WHERE  (
   o.type = ‘V’
)

—————————————–
— List only the functions in this database
—————————————–
SELECT    
 o.name AS [Funtion Name],
 o.type,
 c.name AS [Col Name],
 s.name AS [Col Type],
 c.prec,
 c.scale,
 c.isnullable
FROM       
 dbo.sysobjects AS o
INNER JOIN
 dbo.syscolumns AS c
  ON
   c.id = o.id
INNER JOIN
 dbo.systypes AS s
  ON
   c.xtype = s.xtype
WHERE  (
   o.type = ‘TF’
)

%d bloggers like this: