Sunil Reddy Enugala

…..is just walking through MSBI Street.

List of Database Objects in SQL Server

Posted by Sunil Reddy Enugala on August 21, 2012

Hi,  I would like post on SQL Scripts which I use mostly to get list of database objects like Tables,Views,Triggers,Functions etc.,

List of Views in a database

   1: /*************************List of views in DB************************/

   2: -- using INFORMATION_SCHEMA:

   3: SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,VIEW_DEFINITION 

   4: FROM INFORMATION_SCHEMA.Views

   5: ORDER BY TABLE_NAME;

   6:  

   7: SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE 

   8: FROM INFORMATION_SCHEMA.TABLES 

   9: WHERE TABLE_TYPE='VIEW'

  10: ORDER BY TABLE_NAME;

  11:  

  12: -- using the sp_tables system stored procedure:

  13: EXEC sp_tables @table_type = "'VIEW'";

  14:  

  15: -- using the system tables:

  16: SELECT name

  17: FROM sysobjects

  18: WHERE xtype = 'V';

  19:  

  20: -- using the catalog views:

  21: SELECT name FROM sys.views;

 

List of Tables in a database

   1: /*******************List of user defined tables in DB***************/

   2: -- using sp_tables stored procedure:

   3: EXEC sp_tables @table_type = "'TABLE'";

   4:  

   5: -- using INFORMATION_SCHEMA:

   6: SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE 

   7: FROM INFORMATION_SCHEMA.TABLES

   8: WHERE TABLE_TYPE = 'BASE TABLE'

   9: ORDER BY TABLE_SCHEMA,TABLE_NAME;

  10:  

  11: -- using the system tables:

  12: SELECT name

  13: FROM sysobjects

  14: WHERE xtype = 'U';

  15:  

  16: -- using the catalog views:

  17: SELECT NAME FROM sys.tables;

 

List of Users in a database

   1: /*******************List of Users in DB*********************/

   2: SELECT name FROM sysusers;

 

List of Indexes on a Table

   1: /*******************List indexes on Table***************************/ 

   2: EXEC sp_helpindex '<table with schema>';

 

List of Constraints on a table and its details

   1: /*******************List Constraints on Table*************************/ 

   2: EXEC sp_helpconstraint '<table with schema>';

   3:  

   4: /**********************Constraint Information*************************/

   5: SELECT k.table_name,

   6: K.TABLE_SCHEMA,

   7:  k.column_name field_name,

   8:  c.constraint_type,

   9:  CASE c.is_deferrable WHEN 'NO' THEN 0 ELSE 1 END 'is_deferrable',

  10:  CASE c.initially_deferred WHEN 'NO' THEN 0 ELSE 1 END 'is_deferred',

  11:  rc.match_option 'match_type',

  12:  rc.update_rule 'on_update',

  13:  rc.delete_rule 'on_delete',

  14:  ccu.table_name 'references_table',

  15:  ccu.column_name 'references_field',

  16:  k.ordinal_position 'field_position'

  17:  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k

  18:  LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c

  19:  ON k.table_name = c.table_name

  20:  AND k.table_schema = c.table_schema

  21:  AND k.table_catalog = c.table_catalog

  22:  AND k.constraint_catalog = c.constraint_catalog

  23:  AND k.constraint_name = c.constraint_name

  24: LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc

  25:  ON rc.constraint_schema = c.constraint_schema

  26:  AND rc.constraint_catalog = c.constraint_catalog

  27:  AND rc.constraint_name = c.constraint_name

  28: LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu

  29:  ON rc.unique_constraint_schema = ccu.constraint_schema

  30:  AND rc.unique_constraint_catalog = ccu.constraint_catalog

  31:  AND rc.unique_constraint_name = ccu.constraint_name

  32:  WHERE k.constraint_catalog = DB_NAME()

  33:  AND k.table_name = '<table name>'

  34:  AND k.TABLE_SCHEMA='<schema name>'

  35:  AND k.constraint_name = '<Constraint Name>'

  36:  ORDER BY k.constraint_name,

  37:  k.ordinal_position;

 

List of Columns in a Table and its details

   1: /********************************List of Table fields******************/

   2: --Using system tables 

   3: SELECT c.name,o.name

   4:  FROM sys.columns c

   5:  JOIN sys.objects o ON c.object_id = o.object_id

   6:  WHERE o.name = '<Table Name>'

   7:  AND o.schema_id=<Schema Name>

   8:  

   9: -- with INFORMATION_SCHEMA:

  10: SELECT column_name,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME

  11:  FROM INFORMATION_SCHEMA.COLUMNS

  12:  WHERE table_name = '<Table Name>'

  13:  AND TABLE_SCHEMA='<Schema Name>';

  14:  

  15: --Using System procedures for details information of table

  16: EXEC sys.sp_help '<Table with Schema>';

 

List of Triggers in a database and its details

   1: /*********************List of Triggers for a given table****************/

   2: --Using sysobjects table

   3: SELECT o.name

   4:  FROM sysobjects o

   5:  WHERE xtype = 'TR'

   6:  --AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0

   7:  AND object_name(parent_obj) = '<Table Name>';

   8:  

   9: --Using sys.triggers table

  10: SELECT name

  11:  FROM sys.triggers

  12:  WHERE is_ms_shipped = 0

  13:  AND object_name(parent_id) = '<Table Name>';

  14:  

  15:  --using System procedure

  16:  EXEC sys.sp_helptrigger '<Table Name with Schema>';

  17:  

  18: --detail information of a Trigger

  19: SELECT sys1.name trigger_name,sys2.name table_name,c.text trigger_body

  20: ,c.encrypted is_encripted,

  21:        CASE

  22:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsTriggerDisabled') = 1 

  23:         THEN 0 ELSE 1 END trigger_enabled,

  24:        CASE

  25:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsertTrigger') = 1 THEN 'INSERT'

  26:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsUpdateTrigger') = 1 THEN 'UPDATE'

  27:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsDeleteTrigger') = 1 

  28:         THEN 'DELETE' END trigger_event,

  29:        CASE 

  30:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsteadOfTrigger') = 1 

  31:         THEN 'INSTEAD OF' ELSE 'AFTER' END trigger_type

  32: FROM sysobjects sys1

  33: JOIN sysobjects sys2 ON sys1.parent_obj = sys2.id

  34: JOIN syscomments c ON sys1.id = c.id

  35: WHERE sys1.xtype = 'TR';

  36:  

 

List of Functions in a Database

   1: /*****************List of Function in DB***************************/

   2: --Using System tables

   3: SELECT name

   4: FROM sysobjects

   5: WHERE xtype IN ('TF', 'FN', 'IF','AF');

   6:  

   7: -- with INFORMATION_SCHEMA:

   8: SELECT ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE

   9:       ,DATA_TYPE,ROUTINE_BODY,ROUTINE_DEFINITION

  10: FROM INFORMATION_SCHEMA.ROUTINES

  11: WHERE routine_type = N'FUNCTION';

  12:  

  13: --To modify function

  14: EXEC sys.sp_helptext '<Function Name>'

 

List of Procedures in a database

   1: /*****************List of Procedures in DB***************************/

   2: --Using System Tables

   3: SELECT name

   4: FROM sysobjects

   5: WHERE xtype IN ('P', 'RF', 'X', 'PC');

   6:  

   7: SELECT name FROM sys.procedures;

   8:  

   9: -- with INFORMATION_SCHEMA:

  10: SELECT ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE

  11:       ,DATA_TYPE,ROUTINE_BODY,ROUTINE_DEFINITION

  12: FROM INFORMATION_SCHEMA.ROUTINES

  13: WHERE routine_type = N'PROCEDURE';

  14:  

  15: --To Modify Procedures

  16: EXEC sys.sp_helptext '<Procedure Name>'

About these ads

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

 
Follow

Get every new post delivered to your Inbox.

Join 662 other followers

%d bloggers like this: