Sql 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.

SELECT
SysObjects.[Name] as TableName,
SysColumns.[Name] as ColumnName,
SysTypes.[Name] As DataType,
SysColumns.[Length] As Length
FROM
SysObjects INNER JOIN SysColumns
ON SysObjects.[Id] = SysColumns.[Id]
INNER JOIN SysTypes
ON SysTypes.[xtype] = SysColumns.[xtype]
WHERE
SysObjects.[type] = ‘U’
ORDER BY     SysObjects.[Name]

SELECT

SysObjects.[Name] as TableName,

SysColumns.[Name] as ColumnName,

SysTypes.[Name] As DataType,

SysColumns.[Length] As Length

FROM

SysObjects INNER JOIN SysColumns

ON SysObjects.[Id] = SysColumns.[Id]

INNER JOIN SysTypes

ON SysTypes.[xtype] = SysColumns.[xtype]

WHERE

SysObjects.[type] = ‘U’

ORDER BY     SysObjects.[Name]

So as when you need to choose Tables where a particular column name is present can be achieved through this

SELECT

SysObjects.[Name] as TableName,

SysColumns.[Name] as ColumnName,

SysTypes.[Name] As DataType,

SysColumns.[Length] As Length

FROM

SysObjects INNER JOIN SysColumns

ON SysObjects.[Id] = SysColumns.[Id]

INNER JOIN SysTypes

ON SysTypes.[xtype] = SysColumns.[xtype]

WHERE

syscolumns.[name]=’ColumnName”

ORDER BY     SysObjects.[Name]

“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.

   C = CHECK constraint
    D = Default or DEFAULT constraint
    F = FOREIGN KEY constraint
  FN = Scalar function
    IF = Inlined table-function
   K = PRIMARY KEY or UNIQUE constraint
   L = Log
   P = Stored procedure
    R = Rule
   RF = Replication filter stored procedure
   S = System table
   TF = Table function
   TR = Trigger
   U = User table
   V = View
  X = Extended stored procedure
 The query shown below displays all the triggers in selected database.

 SELECT
       b.[Name] as [Table Name],
       a.[Name] as [Trigger Name],
        a.[crdate] as [Created Date]
   FROM
        SysObjects a
        INNER JOIN Sysobjects b
        ON a.[parent_obj] = b.[id]
    WHERE
       a.[type] = 'TR'
   ORDER BY
      b.[Name]


Leave a Reply

Your email address will not be published. Required fields are marked *

Bitnami