DevPinoy.org
A Filipino Developers Community
   
HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes

I have been working more and more with data recently and its a painstaking task. Alot of my time includes analysis of trades and financial information to create and calculate error free result sets.

This also includes data cleanup..

My task today was to create positional and transactional data for investments. Since the company that i am working for right now is thinking of consolidating the data into one system instead of two(theres two system that basically handles the same thing).This involves digging into one of the biggest database that i have seen and finding out to properly extract data without any idea on how they structured their database (the 3rd party vendor is selling us services to do this but the company declined since it would cost alot of money.)

So here i am digging to every column, every table, every function and every view. I realized that it would be pretty hard to manually go to every database to discover what columns it contain(since there was no db diagram provided by the vendor).

The programmer inside me thought of using SQL SMO/DMO since this would just require me tho modify one of my previous demo regarding this subject.

but... i decided not too since this would be just a one time thing and can be accomplished using sql scripts and just pasting the resultset to Excel so that i could have a suitable documentation for the database structure.

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.

[code language="T-SQL"]

-----------------------------------------
-- 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'
)

[/code]

hmm.. now i need to go back to work :) have fun!


Posted 06-17-2006 1:52 PM by keithrull
Filed under:

Comments

bonskijr wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 06-17-2006 10:46 PM
hi keith,

Good post!! You can btw do the same with the INFORMATION_SCHEMA views, in case they (Sql server team) decide to change the schema/codes of the system tables or your DBA prevents you from accessing it directly:

SELECT tb.TABLE_NAME,
tc.COLUMN_NAME,
tc.DATA_TYPE,
tc.CHARACTER_MAXIMUM_LENGTH,
tc.NUMERIC_PRECISION,
tc.IS_NULLABLE, tb.TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES tb INNER JOIN
INFORMATION_SCHEMA.COLUMNS tc ON
(tb.TABLE_CATALOG = tc.TABLE_CATALOG) AND
(tb.TABLE_NAME    = tc.TABLE_NAME   ) AND
(tb.TABLE_SCHEMA  = tc.TABLE_SCHEMA )
WHERE tb.TABLE_TYPE = 'BASE TABLE'


SELECT tb.TABLE_NAME,
tc.COLUMN_NAME,
tc.DATA_TYPE,
tc.CHARACTER_MAXIMUM_LENGTH,
tc.NUMERIC_PRECISION,
tc.IS_NULLABLE, tb.TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES tb INNER JOIN
INFORMATION_SCHEMA.COLUMNS tc ON
(tb.TABLE_CATALOG = tc.TABLE_CATALOG) AND
(tb.TABLE_NAME    = tc.TABLE_NAME   ) AND
(tb.TABLE_SCHEMA  = tc.TABLE_SCHEMA )
WHERE tb.TABLE_TYPE = 'VIEW'

SELECT tb.ROUTINE_NAME,
tc.COLUMN_NAME,
tc.DATA_TYPE,
tc.CHARACTER_MAXIMUM_LENGTH,
tc.NUMERIC_PRECISION,
tc.IS_NULLABLE
FROM INFORMATION_SCHEMA.ROUTINES tb INNER JOIN
INFORMATION_SCHEMA.ROUTINE_COLUMNS tc ON
(tb.ROUTINE_CATALOG = tc.TABLE_CATALOG) AND
(tb.ROUTINE_NAME    = tc.TABLE_NAME  ) AND
(tb.ROUTINE_SCHEMA  = tc.TABLE_SCHEMA )




keithrull wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 06-18-2006 9:30 PM
oh yeah! i almost forgot about this one. thanks bonskijr!
keithrull wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 06-18-2006 9:37 PM
have you tried looking at the code of this views? they reference sysobjects, syscolumns and systypes :)
bonskijr wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 06-18-2006 10:21 PM
ya... the views reference those same system objects, but as always the views protects you from schema changes so if ever magbago, it won't (usually) break your code..
keithrull wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 06-19-2006 9:37 AM
hmmm..  but if they refence the same object then the views would also break if something changes on the underlying object (sysobjects).

i think either way we arew screwed :)
Derik wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 03-28-2007 8:52 PM

Thanks much for this article - I was trying to find what each numeric data type meant - great stuff!

Derik

Jake wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 07-25-2007 8:29 AM

Great code!  When list functions though, 'TF' will only list table-valued functions.  To see the scalar-valued functions, use "WHERE (o.type='FN')

Skev wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 02-11-2008 9:48 AM

Fantastically useful article.   This is what the web was designed for.

GC wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 03-30-2008 3:13 AM

Great article.  It is very helpfull for me.

Keep it up?

CesarF wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 08-20-2008 6:41 PM

Very helpful article, thank you guys!

Here are my two cents:

With sysobjects and syscolumns tables you have to identify external (outside current db) ones as <db>.<owner>.<systbl>

But with information_schema you've to use only <db>.<view>, THAT IS: WITHOUT OWNER...

ram wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 09-23-2008 11:20 PM

how to get list of database names based on server? plz help

Akshaya wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 09-29-2008 4:52 AM

Hi,Thank u guys

its very useful article

being a fresher,i helped me lot ,,,,,,

once again thanks

Akshaya wrote re: HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes
on 09-29-2008 5:24 AM

Great work

Tims Blog » Blog Archive » Not ready for prime time wrote Tims Blog &raquo; Blog Archive &raquo; Not ready for prime time
on 10-07-2008 8:04 PM

Pingback from  Tims Blog  » Blog Archive   » Not ready for prime time

Copyright DevPinoy 2005-2008