SQL Server Important System Views and Tables

This article list out the important system tables, stored procedure and views.

Introduction

 
In this article I have listed few methods to know about the list of database, tables, views,etc.., It will be very useful when we trace the database objects in the query window. Even though it can be accessible in the sql server object explorer, but when we write the query it can be customized. That means it can filter the result set based on our requirement.
 

How to list out the available database in the SQL Server current connection?

 
Method 1
  1. SP_DATABASES  
Method 2
  1. SELECT name FROM SYS.DATABASES  
Method 3
  1. SELECT name FROM SYS.MASTER_FILES  
Method 4
  1. SELECT * FROM SYS.MASTER_FILES -- Type=0 for .mdf and type=1 for .ldf  
The sp_databases is a system stored procedure it can be listed the database with the size.
 
The sys.databases will list the databases, created date, modified date and database id along with the other information
 
The SYS.MASTER_FILES will query the database details like the database id, size, physical storage path and list both mdf and ldf.
 

How to list the user tables in the database?

 
The following method can be used to get the list of user tables in the SQL server.
 
Method 1
  1. SELECT name FROM SYS.OBJECTS WHERE type='U'  
Method 2
  1. SELECT NAME FROM SYSOBJECTS WHERE xtype='U'  
Method 3
  1. SELECT name FROM SYS.TABLES  
Method 4
  1. SELECT name FROM SYS.ALL_OBJECTS WHERE type='U'  
Method 5
  1. SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'  
Method 6
  1. SP_TABLES  

How to list out the Stored Procedures in the database?

 
Method 1
  1. SELECT name FROM SYS.OBJECTS WHERE type='P'  
Method 2
  1. SELECT name FROM SYS.PROCEDURES  
Method 3
  1. SELECT name FROM SYS.ALL_OBJECTS WHERE type='P'
Method 4
  1. SELECT NAME FROM SYSOBJECTS WHERE xtype='P'
Method 5
  1. SELECT Routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'  
The SYS.OBJECTS table has the common table that has the list for all the procedure, table, triggers, views,etc.., Here procedure can be filtered using the type='p'.
 
The Information_schema.routines is a view that has used in the SQL server 7.0 version. Now exclusive table available for the stored procedure.
 

How to list all Views in the database?

 
Method 1
  1. SELECT name FROM SYS.OBJECTS WHERE type='V'  
Method 2
  1. SELECT name FROM SYS.ALL_OBJECTS WHERE type='V'  
Method 3
  1. SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS  
Method 4
  1. SELECT name FROM SYS.VIEWS

How to list out the Functions in the database?

 
Method 1
  1. SELECT name FROM SYS.OBJECTS WHERE type='IF' -- inline function  
Method 2
  1. SELECT name FROM SYS.OBJECTS WHERE type='TF' -- table valued function  
Method 3
  1. SELECT name FROM SYS.OBJECTS WHERE type='FN' -- scalar function  
Method 4
  1. SELECT name FROM SYS.ALL_OBJECTS WHERE type='IF' -- inline function  
Method 5
  1. SELECT name FROM SYS.ALL_OBJECTS WHERE type='TF' -- table valued function  
Method 6
  1. SELECT name FROM SYS.ALL_OBJECTS WHERE type='FN' -- scalar function
Method 7
  1. SELECT Routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'
Note: IF - Inlined Function, TF- Table valued function, FN- Scalar Function
 

How to get the Triggers in the database?

 
Method 1
  1. SELECT * FROM SYS.TRIGGERS 
Method 2
  1. SELECT * FROM SYS.OBJECTS WHERE type='TR'

How to get the triggers in a table?

 
Method 1
  1. SP_HELPTRIGGER Products
Method 2
  1. SELECT * FROM SYS.TRIGGERS WHERE parent_id = object_id('products')

How to get the columns in a table?

 
Method 1
  1. SP_HELP Products
Method 2
  1. SP_COLUMNS Products
Method 3
  1. SELECT * FROM SYS.COLUMNS WHERE object_id = object_id('Products')
Method 4
  1. SELECT COLUMN_NAME,Ordinal_position,Data_Type,character_maximum_length  
  2. FROM INFORMATION_SCHEMA.COLUMNS   
  3. WHERE TABLE_NAME='Products' 

How to find the Columns in the table?

 
Method 1
  1. SELECT O.name FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C   
  2. ON C.Object_ID =O.Object_ID   
  3. WHERE C.name LIKE '%ShipName%' 
Method 2
  1. SELECT OBJECT_NAME(object_id) AS [Table Name]   
  2. FROM SYS.COLUMNS   
  3. WHERE name LIKE '%ShipName%'
Method 3
  1. SELECT TABLE_NAME   
  2. FROM INFORMATION_SCHEMA.COLUMNS   
  3. WHERE COLUMN_NAME LIKE '%ShipName%'

How to get the Total rows in the table?

 
Method 1
  1. SELECT COUNT(@@ROWCOUNT) FROM Products
Method 2
  1. SELECT COUNT (ProductID) FROM Products
Method 3
  1. SELECT OBJECT_NAME(id) AS [Table Name],rowcnt   
  2. FROM SYSINDEXES   
  3. WHERE OBJECTPROPERTY(id,'isUserTable')=1 AND indid < 2   
  4. ORDER BY rowcnt DESC
Method 4
  1. SELECT rowcnt FROM sysindexes   
  2. WHERE id = OBJECT_ID('Products'AND indid < 2
Method 5
  1. SELECT OBJECT_NAME(OBJECT_ID) TableName,row_count   
  2. FROM sys.dm_db_partition_stats   
  3. WHERE object_id = object_id('Products'AND index_id < 2 

How to get the Check Constraints in the database?

 
Method 1
  1. SELECT * FROM SYS.OBJECTS WHERE type='C'
Method 2
  1. SELECT * FROM sys.check_constraints

How to find the Indexes in the table?

 
Method 1
  1. sp_helpindex Products
Method 2
  1. SELECT * FROM sys.indexes   
  2. WHERE object_id = object_id('products')

How to view the View schema definition?

 
Method 1
  1. SELECT OBJECT_NAME(id) AS [View Name],text   
  2. FROM SYSCOMMENTS   
  3. WHERE id IN (SELECT object_id FROM SYS.VIEWS) 
Method 2
  1. SELECT * FROM sys.all_sql_modules   
  2. WHERE object_id IN (SELECT object_id FROM SYS.VIEWS)
Method 3
  1. SP_HELPTEXT ViewName

How to find the table used in the stored procedure?

 
Method 1
  1. SELECT OBJECT_NAME(id) FROM SYSCOMMENTS S INNER JOIN SYS.OBJECTS O ON O.Object_Id = S.id  
  2. WHERE S.text LIKE '%Products%'  
  3. AND O.type='P'

Conclusion

 
I hope that the above methods will help you more when you work the query window to find the database objects. Please post your feedback and corrections about this article.