Querying Object Information from SQL Server Database, Like stored proc, viewa, functions, triggeres and more.......

Hi

1. Query a list of all stored procedures
=>SELECT * FROM sys.procedures;

2. Query a list of List all Views
=>SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published,
 is_schema_published
 FROM sys.all_objects
 WHERE (is_ms_shipped = 0) AND (type_desc = 'VIEW')
 
 3. Query a list of List all triggers
 =>SELECT *
 FROM sys.objects
 WHERE type_desc = 'SQL_TRIGGER'
 4. Query a list of List all functions
=>
SELECT *
 FROM sys.objects
 WHERE type_desc = 'SQL_SCALAR_FUNCTION'

How to list out the available database in the sql sever  current connection? 

Method 1 : SP_DATABASES

Method 2 : SELECT name FROM SYS.DATABASES

Method 3 : SELECT name FROM SYS.MASTER_FILES

Method 4 : 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 the 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 : SELECT name FROM SYS.OBJECTS WHERE type='U'

Method 2 : SELECT NAME FROM SYSOBJECTS WHERE xtype='U'

Method 3 : SELECT name FROM SYS.TABLES

Method 4 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='U'

Method 5 : SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

Method 6 : SP_TABLES 

How to list out the Stored Procedures in the database?: 

Method 1 : SELECT name FROM SYS.OBJECTS WHERE type='P'

Method 2 : SELECT name FROM SYS.PROCEDURES

Method 3 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='P'

Method 4 : SELECT NAME FROM SYSOBJECTS WHERE xtype='P'

Method 5 : 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 avaiable for the stored procedure. 

How to list all Views in the database? 

Method 1 : SELECT name FROM SYS.OBJECTS WHERE type='V'

Method 2 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='V'

Method 3 : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

Method 4 : SELECT name FROM SYS.VIEWS 

How to listout the Functions in the database? 

Method 1 : SELECT name FROM SYS.OBJECTS WHERE type='IF' -- inline function

Method 2 : SELECT name FROM SYS.OBJECTS WHERE type='TF' -- table valued function

Method 3 : SELECT name FROM SYS.OBJECTS WHERE type='FN' -- scalar function

Method 4 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='IF' -- inline function

Method 5 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='TF' -- table valued function

Method 6 : SELECT name FROM SYS.ALL_OBJECTS WHERE type='FN' -- scalar function

Method 7 : 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 : SELECT * FROM SYS.TRIGGERS

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

How to get the triggers in a table? 

Method 1 : SP_HELPTRIGGER Products

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

How to get the columns in a table? 

Method 1 : SP_HELP Products

Method 2 : SP_COLUMNS Products

Method 3 : SELECT * FROM SYS.COLUMNS WHERE object_id = object_id('Products')

Method 4 : SELECT COLUMN_NAME,Ordinal_position,Data_Type,character_maximum_length FROM  

                     INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Products' 

How to find the Columns in the table? 

Method 1 : SELECT O.name FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C ON C.Object_ID =

                     O.Object_ID  WHERE C.name LIKE '%ShipName%'

Method 2 : SELECT OBJECT_NAME(object_id) AS [Table Name] FROM SYS.COLUMNS WHERE name LIKE

                     '%ShipName%'

Method 3 : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME

                     LIKE '%ShipName%' 

How to get the Total rows in the table? 

Method 1 : SELECT COUNT(@@ROWCOUNT) FROM Products

Method 2 : SELECT COUNT (ProductID) FROM Products

Method 3 : SELECT OBJECT_NAME(id) AS [Table Name],rowcnt FROM SYSINDEXES

                     WHERE OBJECTPROPERTY(id,'isUserTable')=1 AND indid < 2 ORDER BY rowcnt DESC

Method 4 : SELECT  rowcnt FROM sysindexes WHERE id = OBJECT_ID('Products') AND  indid < 2

Method 5 : SELECT OBJECT_NAME(OBJECT_ID) TableName,row_count FROM sys.dm_db_partition_stats

                     WHERE object_id = object_id('Products') AND  index_id < 2 

How to get the Check Constraints in the database? 

Method 1 : SELECT * FROM SYS.OBJECTS WHERE type='C'

Method 2 : SELECT * FROM sys.check_constraints 

How to find the Indexes in the table? 

Method 1 : sp_helpindex Products

Method 2 : SELECT * FROM sys.indexes WHERE  object_id = object_id('products') 

How to view the View schema definition? 

Method 1 :  SELECT OBJECT_NAME(id) AS [View Name],text FROM SYSCOMMENTS WHERE id IN (SELECT  

                      object_id FROM SYS.VIEWS)

Method 2 : SELECT * FROM sys.all_sql_modules WHERE object_id IN (SELECT object_id FROM

                     SYS.VIEWS)

Method 3 : SP_HELPTEXT ViewName 

How to find the table used in the stored procedure? 

Method 1 : SELECT OBJECT_NAME(id) FROM SYSCOMMENTS S

                     INNER JOIN SYS.OBJECTS O ON O.Object_Id = S.id

                    WHERE S.text LIKE '%Products%'

                    AND O.type='P' 

I have written tree view for all the database, you can look into that as well.

http://www.c-sharpcorner.com/UploadFile/skumaar_mca/tree-view-control-with-an-example/