Naveen's Weblog

Bridge to future

Search metadata

Posted by codingsense on December 23, 2008

Many times we may want to see the information of our MSSQL objects. Here are some list of searches i have come across and found useful to share with you all.


    The below query gives the list of keys associated with a table

    select name, xtype from sysobjects where parent_obj = object_id(N'[dbo].TableName')

    You can further filter it out by adding where clause to get only Primary Key( Xtype = ‘PK’) and foreign key (Xtype = ‘F’) and so on

    XType List :
    C: Check constraint
    D: Default constraint
    F: Foreign Key constraint
    L: Log
    P: Stored procedure
    PK: Primary Key constraint
    RF: Replication Filter stored procedure
    S: System table
    TR: Trigger
    U: User table
    UQ: Unique constraint
    V: View
    X: Extended stored procedure


    To List the Tables in the database with thier properites use


    To see the list of stored procedures in the database with thier properites use


    Information schema to find foreign keys associated with a table
    More Information_Schema


    Many people use count(*) to get the number of rows in a table, for this operation the MSSQL server recounts the rows thus leading to slower process. The table properties will have its rowcount at any point of time. The best way is to access from sysindexes as follows

    select rows from SYSINDEXES WHERE ID = OBJECT_ID('TableName') and indid < 2

Happy Learning šŸ™‚


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s