Friday, February 08, 2008

Query to find all the tables and columns in selected database.

         As a developer, it is really important for us to understand database design and underlying tables used in application. Sometime we do not have direct access to database server so that we can not open the server console and look in to the database.

         In this case we can take help of SysObjects, SysColumns, SysTypes tables of SQL Server 2005. These tables stores the information about each tables and columns and their data types. Using this tables you can write the query to find out all the tables and columns in selected database. Below is the query that gives you all the table and columns for those tables with data types and length.


   1: SELECT 
   2:     SysObjects.[Name] as TableName,
   3:     SysColumns.[Name] as ColumnName,
   4:     SysTypes.[Name] As DataType,
   5:     SysColumns.[Length] As Length
   6: FROM
   7:     SysObjects INNER JOIN SysColumns
   8:     ON SysObjects.[Id] = SysColumns.[Id]
   9:     INNER JOIN SysTypes 
  10:     ON SysTypes.[xtype] = SysColumns.[xtype]
  11: WHERE
  12:     SysObjects.[type] = 'U'
  13: ORDER BY 
  14:     SysObjects.[Name]

Fig - (1) Query to find all the tables and columns for those tables.


           "Type" columns of SysObjects table represent the different objects available in database (like Table,Trigger,Stored Procedures etc.). Below list explains the different values of "Type" columns.

   1: C = CHECK constraint 
   2: D = Default or DEFAULT constraint
   3: F = FOREIGN KEY constraint 
   4: FN = Scalar function
   5: IF = Inlined table-function
   6: K = PRIMARY KEY or UNIQUE constraint 
   7: L = Log
   8: P = Stored procedure
   9: R = Rule
  10: RF = Replication filter stored procedure
  11: S = System table 
  12: TF = Table function
  13: TR = Trigger
  14: U = User table
  15: V = View
  16: X = Extended stored procedure

Fig - (2) Explanation of different values of "Type" columns in SysObjects table.


          For more detail refer SysObjects and SysColumns.  and So in fig - (1) query uses [type] = 'U'. This means query displays al the user tables. You can change the condition in WHERE clause to get different objects. The query shown below displays all the triggers in selected database.


   1: SELECT 
   2:     b.[Name] as [Table Name],
   3:     a.[Name] as [Trigger Name],
   4:     a.[crdate] as [Created Date] 
   5: FROM 
   6:     SysObjects a 
   7:     INNER JOIN Sysobjects b
   8:     ON a.[parent_obj] = b.[id]
   9: WHERE 
  10:     a.[type] = 'TR'
  11: ORDER BY 
  12:     b.[Name]

Fig - (3) Query to display all the Triggers for selected database.


Happy Programming !!

No comments: