Tuesday, August 10, 2010

System Objects

SYSTEM VARIABLES


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

MAPPING 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
  • When 0 means no index available
  • When 1 means clustered index available
  • When 2 means non-clustered index
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