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"

PRINT in SQL Server

• Returns a user-defined message to the client. 
• Message should have string ((n)varchar) data type.
• If we are planning to PRINT any variable value and that variable is not of string data type then only after converting it to string data type, we can print that.
• A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string.
• Print also help us in debugging.

For example:-

a) PRINT a user defined message :-

b) PRINT Variable value having string data type:-

c) PRINT variable value not having string data type:-

 

TOP n PERCENT

TOP n [PERCENT]

With the help of this we can limit the result set.

In the case of TOP, by default it will return the n number of rows of total result set but if we use PERCENT with TOP then it will return the n PERCENT record of total result set.

PERCENT is optional with TOP.

For example:-

Statistics in SQL Server

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Error Function

Below are some error functions, which help us in debugging:-

Function Name	Description
ERROR_MESSAGE()	Returns the complete description of the error message
ERROR_NUMBER()	Returns the number of the error
ERROR_SEVERITY()	Returns the number of the Severity
ERROR_STATE()	Returns the error state number
ERROR_PROCEDURE()	Returns the name of the stored procedure where the error occurred
ERROR_LINE()	Returns the line number that caused the error

FacebookPage

@@TranCount

It returns the number of active transaction in current connection.

When transaction within a transaction starts, it increase @@TranCount by 1. COMMIT and ROLLBACK decrease @@TranCount by 1 except for ROLLBACK TRANSACTION <savepoint_name>, which does not affect @@TRANCOUNT.

 - facebook_page

@@TranCount and XACT_STATE()

Difference between @@TranCount and Xact_State() :-

Both @@TranCount and Xact_State() are use to get the information about transaction in SQL Server.

The major difference between @@TranCount and Xact_State() is, @@TRANCOUNT does not report uncommittable transactions and XACT_STATE() does not report the transaction nesting level.