How to know data type of a column

With the help of below query, we can get the data type of a column:-

SELECT TABLE_NAME
, COLUMN_NAME
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%<Column Name>%'

follow over facebook @ "SQL-Horizons"

List of stored procedures depend on a table

There is a Dynamic Management Function introduced with SQL Server 2008 which help us to get the list of stored procedures depend on a table.

Below is the query:-

SELECT referencing_schema_name
, referencing_entity_name 'StoredProcedureName'
, referencing_class_desc
FROM sys.dm_sql_referencing_entities('<schema>.<TableName>','OBJECT')

Note:- While specifying the table name please include schema name also, otherwise result will not display the dependent stored procedure.

Example:-

Create table

CREATE TABLE tbl (id INT)
GO

Create Stored Procedure which refer table "tbl"

CREATE PROC dbo.proc_test
AS
BEGIN
 
SELECT *
FROM tbl1

END 
GO

Lets create one another stored procedure which refer table "tbl"

CREATE PROC dbo.proc_test2
AS
BEGIN
 
INSERT INTO tbl VALUES (1)

END 
GO

Now time to figure out name of stored procedures depend on table "tbl" with the help of below query:-

SELECT referencing_schema_name
, referencing_entity_name 'StoredProcedureName'
, referencing_class_desc
FROM sys.dm_sql_referencing_entities('dbo.tbl','OBJECT')

Result of above query

referencing_schema_name StoredProcedureName referencing_class_desc
dbo                                          proc_test                         OBJECT_OR_COLUMN
dbo                                          proc_test2                 OBJECT_OR_COLUMN


follow over facebook @ "SQL-Horizons"

List of tables referenced by stored procedure

There is a Dynamic Management Function introduced with SQL Server 2008 which help us to get the list of tables referenced by a stored procedure.

Below is the query:-

SELECT referenced_schema_name
, referenced_entity_name 'TableName'
, referenced_minor_name 'ColumnName'
, referenced_id
, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('<Schema>.<StoredProcedure>','OBJECT')

Note:- While specifying the stored procedure name please include schema name also, otherwise result will not display the dependencies.

Example:-

Create table 1

CREATE TABLE tbl1 (id INT)
GO

Create table 2

CREATE TABLE tbl2 (id INT)
GO

Create Stored Procedure which refer tbl1 & tbl2

CREATE PROC dbo.proc_test
AS
BEGIN

SELECT *
FROM tbl1 a
INNER JOIN tbl2 b
ON a.id = b.id

END
GO

Now time to figure out tables referenced by the stored procedure "proc_test" with the help of below query:-

SELECT referenced_entity_name 'TableName'
, referenced_minor_name 'ColumnName'
, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('dbo.proc_test','OBJECT')

Result of above query

TableName ColumnName referenced_class_desc
tbl1         id OBJECT_OR_COLUMN
tbl2         id OBJECT_OR_COLUMN


follow over facebook @ "SQL-Horizons"