Finding OBJECTS in DB
Sometime we have confusion that the object presents in the database or not. To find the objects present we have to query the sys.sysobjects.
I am trying to make it easy by a stored procedure.
The limitation of the stored procedure is it helps to find such common objects like Scalar function, Inlined table-func, Stored procedure, Table function, Trigger, User define Table and Views only.
It also displays the definition or text of the supplied objects if it is not encrypted.
Please note that this is a prototype only. You can enrich the functionality for betterment.
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID('find_Obj'))
BEGIN
DROP PROCEDURE find_Obj
END
GO
CREATE PROCEDURE find_Obj
(
@p_ObjName VARCHAR(MAX)
)
AS
DECLARE @v_TYPE Varchar(MAX)
BEGIN
IF EXISTS(SELECT * FROM sys.sysobjects WHERE id=OBJECT_ID(@p_ObjName))
BEGIN
SELECT OBJECT_NAME(id), case type when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'P' then 'Stored procedure'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'U' then 'User define Table'
when 'V' then 'View' end [Type]
FROM sys.sysobjects
WHERE id=OBJECT_ID(@p_ObjName)
SELECT @v_TYPE=type
FROM sys.sysobjects
WHERE id=OBJECT_ID(@p_ObjName)
-- Worked if not Encrypted
IF @v_TYPE='U'
BEGIN
EXEC('sp_columns '+ @p_ObjName)
END
ELSE IF @v_TYPE='FN'
OR @v_TYPE='FN'
OR @v_TYPE='IF'
OR @v_TYPE='TR'
OR @v_TYPE='V'
BEGIN
EXEC('sp_helptext '+ @p_ObjName)
END
ELSE
BEGIN
SELECT ' Defination Unknown'
END
END
END
GO
Hope you like it.
Sometime we have confusion that the object presents in the database or not. To find the objects present we have to query the sys.sysobjects.
I am trying to make it easy by a stored procedure.
The limitation of the stored procedure is it helps to find such common objects like Scalar function, Inlined table-func, Stored procedure, Table function, Trigger, User define Table and Views only.
It also displays the definition or text of the supplied objects if it is not encrypted.
Please note that this is a prototype only. You can enrich the functionality for betterment.
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID('find_Obj'))
BEGIN
DROP PROCEDURE find_Obj
END
GO
CREATE PROCEDURE find_Obj
(
@p_ObjName VARCHAR(MAX)
)
AS
DECLARE @v_TYPE Varchar(MAX)
BEGIN
IF EXISTS(SELECT * FROM sys.sysobjects WHERE id=OBJECT_ID(@p_ObjName))
BEGIN
SELECT OBJECT_NAME(id), case type when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'P' then 'Stored procedure'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'U' then 'User define Table'
when 'V' then 'View' end [Type]
FROM sys.sysobjects
WHERE id=OBJECT_ID(@p_ObjName)
SELECT @v_TYPE=type
FROM sys.sysobjects
WHERE id=OBJECT_ID(@p_ObjName)
-- Worked if not Encrypted
IF @v_TYPE='U'
BEGIN
EXEC('sp_columns '+ @p_ObjName)
END
ELSE IF @v_TYPE='FN'
OR @v_TYPE='FN'
OR @v_TYPE='IF'
OR @v_TYPE='TR'
OR @v_TYPE='V'
BEGIN
EXEC('sp_helptext '+ @p_ObjName)
END
ELSE
BEGIN
SELECT ' Defination Unknown'
END
END
END
GO
Hope you like it.
View and Underlying Table Information
When we are working with a view in a database, we must know that which table is used to create the view. In this article I am trying to illustrate these points.
To find, all the view name of your current database use.
SELECT name As View_Name
FROM sys.sysobjects WHERE xtype='V'
GO
It shows you the entire view name in your current database.
Fine, now you want to know which underlying table is used in a particular view.
For example, suppose you have a view name "vw_salesInfo" and the view used two tables named "mast_customer", "tran_salesdtl". To see this information many developer open the view definition and search the T-SQL statements to find the underlying Table objects.
This is not a good procedure to find the underlying table objects information from view. To find that use this SQL statements.
SELECT DISTINCT VIEW_NAME,
TABLE_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE VIEW_NAME='vw_salesInfo'
GO
It gives you the view name and underlying table name related to it.
Hope this article is quite informative and thanking you to provide your valuable time on it.
When we are working with a view in a database, we must know that which table is used to create the view. In this article I am trying to illustrate these points.
To find, all the view name of your current database use.
SELECT name As View_Name
FROM sys.sysobjects WHERE xtype='V'
GO
It shows you the entire view name in your current database.
Fine, now you want to know which underlying table is used in a particular view.
For example, suppose you have a view name "vw_salesInfo" and the view used two tables named "mast_customer", "tran_salesdtl". To see this information many developer open the view definition and search the T-SQL statements to find the underlying Table objects.
This is not a good procedure to find the underlying table objects information from view. To find that use this SQL statements.
SELECT DISTINCT VIEW_NAME,
TABLE_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE VIEW_NAME='vw_salesInfo'
GO
It gives you the view name and underlying table name related to it.
Hope this article is quite informative and thanking you to provide your valuable time on it.
System Objects
SYSTEM VARIABLES
MAPPING SYSTEM TABLES
msdb DATABSE TABLES
MAPPING SYSTEM TABLES (msdb database)
SYSTEM FUNCTION
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
|
