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 * |
Table = dbo.sysdiagrams
SELECT * |
2 | Tables | Table = sysobjects
SELECT * |
Table = sys.tables
SELECT * |
3 | Columns | Table = syscolumns
SELECT o.name, c.name |
Table = sys.all_columns
SELECT OBJECT_NAME([Object_ID]) AS ‘TableName’, [Name] AS ‘ColumnName’, Column_ID |
4 | Primary Keys | Table = sysobjects
SELECT p.name, OBJECT_NAME(parent_obj) AS ‘Table Name’ |
Table = sys.objects
SELECT OBJECT_NAME(o.parent_object_id) AS ‘ParentObject’, s.name AS ‘Schema’, o.Name AS ‘PrimaryKey’ |
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’ |
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’ |
6 | Constraints | Table = sysconstraints
SELECT o.[name] AS ‘DefaultName’, OBJECT_NAME(c.[id]) AS ‘TableName’, col.[name] AS ‘ColumnName’ |
Table = sys.objects
SELECT OBJECT_NAME(o.parent_object_id) AS ‘ParentObject’, s.name AS ‘Schema’, o.Name AS ‘PrimaryKey’ |
7 | FileGroups\Partitions | Table = sysfilegroups
SELECT * |
Table = sys.data_spaces
SELECT * |
8 | Stored Procedures | Table = sysobjects
SELECT o.[name], o.[id], o.xtype, c.[text] |
Table = sys.objects
SELECT o.[Name], o.[object_id], o.[type], m.definition |
9 | Functions | Table = sysobjects
SELECT o.[name], o.[id], o.xtype, c.[text] |
Table = sys.objects
SELECT o.[Name], o.[object_id], o.[type], m.definition |
10 | Views | Table = sysobjects
SELECT o.[name], o.[id], o.xtype, c.[text] |
Table = sys.objects
SELECT o.[Name], o.[object_id], o.[type], m.definition |
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’
)