Search metadata

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