Few Global Variables,Functions And Queries In SQL.
This article explains few global variables and functions like @@ROWCOUNT, SCOPE_IDENTITY, DBCC CHECKIDENT, IDENTITY_INSERT and few queries to retrieve any keywords, all the tables or stored proc used in a database.
FEW SYSTEM DEFINED KEYWORDS AND FUNCTIONS IN SQL.
@@ROWCOUNT returns the total number of rows affected in the last operation .i.e. SELECT/INSERT/UPDATE/DELETE
SCOPE_IDENTITY 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 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 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 procs using a particular parameter: -
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE PARAMETER_NAME like '%CUS_ID%'
Query to Find list of all the stored procs using a specific term: -
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME like '%Account%'
Query to Find list of all the stored procs using a particular parameter: -
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='YourSprocName'
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'