Variable | Description |
@@ERROR | Contains error number (defined by SQL server) for previous statement. If no error then returns 0 |
@@FETCH_STATUS | 0 à fetch from last statement was successful -1 à last statement got failed -2 àfetch was missing Generally being used in cursor |
@@TRANCOUNT | Total number of open transactions |
@@ROWCOUNT | Total number of rows affected in last statement. Will work with INSERT, UPDATE, DELETE, SELECT. Won’t work with TRUNCATE |
@@IDENTITY | returns the last identity value generated for a specific table in any session and any scope |
@@VERSION | Gives SQL Server version |
SYSTEM TABLES
Table | Description |
sys.sysdatabases / sys.databases | Different info about all the databases available |
sys.syslanguages | Different languages and their info (total 32 languages) |
sys.sysmessages / sys.messages | Contains all SQL server error messages (in all 11 languages) with severity |
Sys.syslogins | Contains all logins available in sql server |
Sys.sysobjects / sys.objects | Contains all objects created in particular database XTYPE column will differentiate those objects U à Table P à Procedure V à View TR à Trigger FN à Scalar function TF / IF à Table function PC à SQL CLR Stored Procedure PK à Primary Key F à Foreign Key C à Check constraint D à Default constraint UQ à Unique constraint |
Sys.systypes | Contains all SQL server defined types + user defined types. “length” column will tell how many number of bytes it is getting stored. Nvarchar(250), it will show as 500. Varchar(250), it will show as 250 Nvarchar(max), it will show as -1 |
Sys.tables | Contains all the tables within a particular db |
Sys.columns | Contains all the columns of all the tables |
Sys.procedures | Contains all the procedures |
Sys.database_files | Contains all the files |
Sys.filegroups | Contains all the file groups |
Sys.indexes | Contains all the indexes for each table. Indid is very important column
For each table, there will be only 1 row for indid = 0/1 |
Sys.index_columns | Contains participant columns for each index |
Sys.sql_modules | Contains complete definition of each SP/Function/Trigger |
msdb DATABSE TABLES
Table | Description |
backupset | Contains backup details of each backup of all databases |
Backupmedafamily | Contains physical path of backup |
Backupfile | |
Sysjobs | Contains all the jobs |
Sysjobsteps | Contains all steps for all jobs |
Sysjobschedules | Contains job schedule for all jobs |
Sysjobhistory | Maintains job execution history at step level. Run_status (0: failed, 1: success) |
Sysjobactivity | Maintains execution history at job level (start time, end time, final status, last execution step id, etc.) |
MAPPING SYSTEM TABLES (msdb database)
SYSTEM FUNCTION
DATE FUNCTIONS | |
GETDATE() | Gives current date time (server’s timezone) |
GETUTCDATE() | Gives current date time (GMT + 0 Timezone) |
ISDATE('Value') | Returns bool value whether given value is proper date or not |
DATEADD(MM,10,GETDATE()) | Adds 10 months into current date |
DATEPART(YYYY, GETDATE()) | Gives only year |
DATEDIFF(dd,'2009-01-01','2010-01-01') | Difference of 2 days |
DATENAME (MM, GETDATE()) | Name of the month |
SELECT DATENAME(DW, DATEADD(DD, 1-DAY(GETDATE()), GETDATE())) | First day of the current month |
CONVERT(VARCHAR,GETDATE(),XX) | various formats of showing date. XX varies from 100 to 114 |
STRING FUNCTIONS | |
SPACE(5) | Adds space 5 times |
LTRIM() | Trims from left |
RTRIM() | Trims from right |
REPLACE('India','I','A') | Replaces “I” with “A” in a string “India” à OUTPUT : AndAa |
CHARINDEX('i','India',1) | Gives position of “I” in a string “India” starting from 1st character à OUTPUT : 1 If CHARINDEX('i','India',2)then OUTPUT : 4 |
SUBSTRING('Nisarg',2,3) | Gives 3 characters from 2nd position in a given string. à OUTPUT : isa |
REPLICATE('blah',3) | Repeats given string 3 times à OUTPUT : blahblahblah |
RIGHT('India',2) | Gives 2 characters from right side à OUTPUT : ia |
LEFT('India',2) | Gives 2 characters from left side à OUTPUT : In |
LEN('Value') | Gives total number of characters available in given string (after right trimming) |
UPPER('inDIA') | Converts all the characters into upper case |
LOWER('inDIA') | Converts all the characters into lower case |
REVERSE('INDIA') | Reverses the string. à OUTPUT : AIDNI |
STUFF('hindustan',3,4,'STUFF') | Replaces 4 characters from 3rd position with given string à OUTPUT : hiSTUFFtan |
MATHEMATICAL FUNCTIONS | |
AVG, SUM, MIN, MAX, COUNT | |
ISNUMERIC | Checks whether given value is numeric or not. Gives Boolean reult |
METADATA FUNCTIONS | |
DB_ID() | Gives ID of a current database |
DB_NAME() | Gives name of current database |
OBJECT_NAME(389576426) | Gives name of the objects of given ID |
OBJECT_ID('dbo.IndexCheck') | Gives ID from sysobjects table of a given object |
SCHEMA_ID('dbo') | Gives ID of a given schema |
SCHEMA_NAME(1) | Gives name of a schema with given ID |
OTHER FUNCTIONS | |
ISNULL(ColumnName,'Value') | Replaces NULL value of a given column with passed string |
CONVERT(DataType,'Value') | Converts particular value into a given datatype if compatible |
SCOPE_IDENTITY() | returns the last identity value generated for any table in the current session and the current scope |
IDENT_CURRENT('TableName') | returns the last identity value generated for a specific table in any session and any scope |
NEWID() | Gives unique GUID |
COALESCE(Column1, Column2, ..., ColumnN) | Gives first Non-NULL value from the list. If all are NULL, SQL server will throw an error |
ERROR_NUMBER() | If written in CATCH block then gives error number of an error which caused to execute CATCH block Outside CATCH block then NULL |
ERROR_MESSAGE() | If written in CATCH block then gives error message of an error which caused to execute CATCH block Outside CATCH block then NULL |
ERROR_PROCEDURE() | If written in CATCH block then gives name of SP/Trigger from where error occurred Outside CATCH block then NULL If error not from SP/Trigger then NULL |
ERROR_LINE() | If written in CATCH block then gives line number from SP/Trigger from where error occurred Outside CATCH block then NULL If error not from SP/Trigger then NULL |
ERROR_SEVERITY() | If written in CATCH block then gives error serverity of an error which caused to execute CATCH block Outside CATCH block then NULL |
