Extract Main Objects from Database

It is really nice and helpful to know what is going on in a given database.

So, I thought to list all the necessary objects in database and how to extract data from them.

1. All the objects
2. All the tables
3. Table Columns
4. All Indexes
5. All Views
6. All Stored Procedures
7. All Functions
8. All Triggers

All the objects

To extract all the objects from your database, sys.objects is the source. It will return all the objects in your current database which they are in the following categories:

•  P = SQL Stored Procedure
• FN = SQL scalar function
• FT = Assembly (CLR) table-valued function
• TR = SQL DML trigger
• IF = SQL inline table-valued function
• TF = SQL table-valued-function
• U = Table (user-defined)
• V = View
• IT = Internal table

Use myDatabase

Go

Select

                 sysObj.object_id As ObjectId,

                 sysObj.name As ObjectName,

                 sysObj.type As ObjectType,

                 sysObj.type_desc As ObjectTypeDesc,

                 create_date As ObjectCreateDate,

                 modify_date As ObjectModifyDate

From

                sys.objects As sysObj

Where

                type In ('P', 'FN', 'FT', 'TR', 'IF', 'TF', 'U', 'V', 'IT')

All the Tables

To list all the tables in a specific database, we will use sys.tables.

Use myDatabase

Go

Select

                object_id As ObjectId,

                name As TableName,

                create_date As TableCreateDate,

                modify_date As TableModifyDate,

                lob_data_space_id As TableLobDataSpaceId,

                max_column_id_used As TableMaxColumnIdUsed

From

                sys.tables

All the Columns

To all list all the columns in tables, we will use sys.columns.

Use myDatabase

Go

Select

                object_id As ObjectId,

                column_id As ColumnId,

                name As ColumnName,

                system_type_id As ColumnSystemTypeId,

                user_type_id As ColumnUserTypeId,

                max_length As ColumnMaxLength,

                precision As ColumnPrecision,

                scale As ColumnScale,

                is_nullable As ColumnIsNullable,

                is_identity As ColumnIsIdentity

From

                sys.columns

Where

                object_id in (Select systbl.object_id from sys.tables as systbl)

All the Indexes

To view the list of all Indexes being used in database:

Use myDatabase

Go

Select

                object_id As ObjectId,

                index_id As IndexId,

                name As IndexName,

                type As IndexType,

                type_desc As IndexTypeDesc,

                is_unique As IndexIsUnique,

                is_primary_key As IndexIsPrimarykey,

                fill_factor As IndexFillFactor

From

                sys.indexes

Where

                type <> 0 And

                object_id in (Select systbl.object_id from sys.tables as systbl)

All the Views

To see the Views SQL statement, sys.views is the main source.

Use myDatabase

Go

Select

                sysView.object_id As ObjectId,

                sysView.name As ViewName,

                sysView.parent_object_id As ViewParentId,

                sysView.create_date As ViewCreateDate,

                sysView.modify_date As ViewModifyDate,

                sysMod.definition As ViewDefinition

From

                sys.views As sysView Inner Join sys.sql_modules sysMod On sysView.object_id = sysMod.object_id

And for column names being listed separately, here is another query which will list them.

Use myDatabase

Go

Select

                sysCols.object_id As ObjectId,

                sysView.name As ViewName,

                sysCols.column_id As ViewColumnId,

                sysCols.name As ViewColumnName,

                sysCols.system_type_id As ViewColumnSystemTypeId,

                sysCols.user_type_id As ViewColumnUserTypeId,

                sysCols.max_length As ViewColumnMaxLength,

                sysCols.precision As ViewColumnPrecision,

                sysCols.scale As ViewColumnScale,

                sysCols.is_nullable As ViewColumnIsNullable,

                sysCols.is_identity As ViewColumnIsIdentity

From

                sys.columns As sysCols inner join sys.views As sysView On sysCols.object_id = sysView.object_id

All the Stored Procedures

To list all the stored procedures, we can use sys.procedures and also sys.numbered_procedures.

The reason for using sys.numbered_procedures is that as you know, you can have numbering inside a stored procedure, like versions of the same stored procedure. But each one could act and do something very different.

Use myDatabase

Go

Select

                sysProc.object_id As ObjectId,

                sysProc.name As ProcedureName,

                Convert(TinyInt, 1) As ProcedureVersion,

                sysProc.parent_object_id As ProcedureParentId,

                sysProc.create_date As ProcedureCreateDate,

                sysProc.modify_date As ProcedureModifyDate,

                sysMod.definition As ProcedureDefinition

from

                sys.procedures As sysProc Inner Join sys.sql_modules sysMod On sysProc.object_id = sysMod.object_id

Union All

Select

                sysProc.object_id As ObjectId,

                sysProc.name As ProcedureName,

                Convert(SmallInt,sysProcNum.procedure_number) As ProcedureVersion,                         

                sysProc.parent_object_id As ProcedureParentId,

                sysProc.create_date As ProcedureCreateDate,

                sysProc.modify_date As ProcedureModifyDate,

                sysProcNum.definition As ProcedureDefinition

from

                sys.procedures As sysProc Inner Join sys.numbered_procedures sysProcNum On sysProc.object_id = sysProcNum.object_id

All the Functions

To list all the function with their definitions, sys.sql_modules is the source. There is NOT any sys object named “sys.functions”

Use myDatabase

Go

Select

                sysFunc.object_id As ObjectId,

                sysFunc.name As FunctionName,

                sysFunc.type As FunctionType,

                sysFunc.type_desc As FunctionTypeDesc,

                sysFunc.create_date As FunctionCreateDate,

                sysFunc.modify_date As FunctionModifyDate,

                sysMod.definition As FunctionDefinition

From

                sys.objects As sysFunc Inner Join sys.sql_modules As sysMod On sysFunc.object_id = sysMod.object_id

Where

                sysFunc.type In ('FN', 'FS', 'FT', 'IF', 'TF')

And for Functions column to be listed separately:

Use myDatabase

Go

Select

                SysFuncCols.object_id As ObjectId,

                SysFunc.name As FunctionName,

                SysFuncCols.column_id As FunctionColumnId,

                SysFuncCols.name As FunctionColumnName,

                SysFuncCols.system_type_id As FunctionColumnSystemTypeId,

                SysFuncCols.user_type_id As FunctionColumnUserTypeId,

                SysFuncCols.max_length As FunctionColumnMaxLength,

                SysFuncCols.precision As FunctionColumnPrecision,

                SysFuncCols.scale As FunctionColumnScale

From

                sys.columns As SysFuncCols Inner Join sys.objects As SysFunc On SysFuncCols.object_id = SysFunc.object_id

Where

                SysFuncCols.object_id in (Select sysFunc.object_id from sys.objects As sysFunc Where sysFunc.type In ('FN', 'FS', 'FT', 'IF', 'TF'))

Also, to list parameters being used in each function:

Use myDatabase

Go

Select

                SysParam.object_id As ObjectId,

                SysFunc.name As FunctionName,

                SysParam.parameter_id As FunctionParamId,

                SysParam.name As FunctionParamName,

                SysParam.system_type_id As FunctionParamSystemTypeId,

                SysParam.user_type_id As FunctionParamUserTypeId,

                SysParam.max_length As FunctionParamMaxLength,

                SysParam.precision As FunctionParamPrecision,

                SysParam.scale As FunctionParamScale

From

                sys.parameters As SysParam Inner Join sys.objects As SysFunc On SysParam.object_id = SysFunc.object_id

Where

                SysFunc.type In ('FN', 'FS', 'FT', 'IF', 'TF') And SysParam.parameter_id <> 0

All the Triggers

To list all the tiggers being used in a database, we can use sys.triggers.

Use myDatabase

Go

Select

      sysTrig.object_id As ObjectId,

      sysTrig.name As TriggerName,

      sysTrig.parent_id As TriggerParentId,

      sysTrig.create_date As TriggerCreateDate,

      sysTrig.modify_date As TriggerModifyDate,      

      sysMod.definition As TriggerDefinition

From

      sys.triggers sysTrig Inner Join sys.sql_modules As sysMod on sysTrig.object_id = sysMod.object_id