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.

  • SYSOBJECTS:

    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

  • INFORMATION_SCHEMA:

    To List the Tables in the database with thier properites use

    
    select * from INFORMATION_SCHEMA.TABLES

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

    
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES

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

  • SYSINDEXES:

    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 šŸ™‚

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s