SQL SERVER global variables and Functions

SQL SERVER global variables and Functions like @@ROWCOUNT, SCOPE_IDENTITY, DBCC CHECKIDENT, IDENTITY_INSERT


@@ROWCOUNT:- It returns the total number of rows affected in the last operation .i.e. SELECT/INSERT/UPDATE/DELETE

SCOPE_IDENTITY: It returns the last value of the identity column of any table in the current session and the current scope. It must be referred immediately after INSERT, SELECT INTO, or bulk copy statement is completed or after an INSERT TRIGGER to get the correct value.

@@IDENTITY: It returns the last value of the identity column of any table in the current session, across all scopes. Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed.

IDENT_CURRENT: It returns the last value of the identity column for a specific table in any session and any scope. It works for a particular table only and returns that table related identity value which is generated in any session or scope.

DBCC CHECKIDENT: It is used to reset the identity Column value of a table.

Syntax

DBCC CHECKIDENT(TABLENAME, RESEED|NORESEED, RESEED_VALUE)

If the RESEED_VALUE is Zero then the IDENTITY Column value would start from 1
If the RESEED_VALUE is One then the IDENTITY Column value would start from 2


Example

DBCC CHECKIDENT('tblArchievalLog', RESEED, 0)


IDENTITY_INSERT: Whenever we try to insert values into an identity column manually or explicitly then we get an Error "Cannot INSERT Into identity Column.IDENTITY_INSERT is SET OFF for the table." 

So we need to set the IDENTITY_INSERT to ON to insert values.This can be done with the below syntax.But we can set the IDENTITY_INSERT ON for only table in a database in a given time. If we need to insert values into the identity column of a second table then we need to set the IDENTITY_INSERT OFF in the first table.

Syntax to insert values into the identity Column of a table

SET IDENTITY_INSERT DATABASENAME.SCHEMANAME.TABLENAME ON|OFF
INSERT INTO DATABASENAME.SCHEMANAME.NEW_TABLENAME(Field1,Field2,Field3,…)
SELECT * FROM DATABASENAME.SCHEMANAME.OLD_TABLENAME

Example

SET IDENTITY_INSERT EDIMonitoringTool.dbo.Transmissions_Backup ON
INSERT INTO EDIMonitoringTool.dbo.Transmissions_Backup(Field1,Field2,Field3,…)
SELECT * FROM EDIMonitoringTool.dbo.Transmissions


Query to Create BACKUP Table for a table: -
This Query can be used to create backup tables across different databases or same database.

Syntax

SELECT * INTO NEW_TABLENAME FROM DATABASENAME.SCHEMANAME.OLD_TABLENAME

Example

 SELECT * INTO dbo.Files_backup FROM EDIMonitoringTool.dbo.Files


Query to Find list of all the stored procedures using a particular parameter 

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE PARAMETER_NAME like '%CUS_ID%'


Query to Find list of all the stored procedures using a specific term

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME like '%Account%'


Query to Find list of all the stored procedures using a particular parameter

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='SprocName'

Query to Find list of all the tables using a particular column name 
                                
Select * From INFORMATION_SCHEMA.COLUMNS Where COLUMN_NAME = 'Colname'


Query to Find list of all the tables in a particular database
                             
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbo'