Tag: Sys.Objects

Search objects like Stored Procedure, View, Table in SQL Server

SearchSearch objects like Stored Procedure, View, Table in SQL Server

It happens many time that we don’t know the exact name of stored procedure or table or view etc. and we have to either look at object explorer and scroll all the way down for object we are looking for or we can use “Filter” facility of SSMS but I prefer one small function I have written very long back because I am a script buddy and try to avoid mouse as long as possible.

Sometime, we may want to find out whether function, table or view we are looking for, is used in any other SP or View to check dependency, I used the same function. BTW, you can use “SP_Depends” or “Information_Schema.routines” to find dependency of the object which is more accurate way for find dependency.

Here is the table valued user defined function which I have developed a long back and still using it. In fact, I am so used to it now.

[sourcecode language=”sql”]

CREATE FUNCTION ObjectSearch

(

@SearchString VARCHAR(100)

)

RETURNS TABLE

RETURN

SELECT

DISTINCT scm.name AS SchemaName,

obj.name AS ObjectName,

obj.type_desc AS ObjectType

FROM sys.objects obj

INNER JOIN syscomments sysCom ON sysCom.Id = obj.object_id

INNER JOIN sys.schemas scm ON scm.schema_id = obj.schema_id

WHERE

is_ms_shipped = 0 AND sysCom.text like ‘%’ + @SearchString + ‘%’

GO

[/sourcecode]

Okay, not we have “ObjectSearch” function in our database. I have created this function in my AdventureWorks2012 database and now I am going to find where “Department” table is used so I would execute following simple SELECT statement with “ObjectSearch” function.

[sourcecode language=”sql”]

SELECT * FROM ObjectSearch(‘Department’)

GO

[/sourcecode]

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Find locked table name with duration and count in SQL Server

Finding LOCK table in SQL Server is one of essential task of DBA. Locked table for long time in SQL Server adversary affect on performance of the application, especially in pick business hours.

I have one SQL Server VIEW which help us to find all the table which are being locked currently along with duration since it is being locked.

[sourcecode language=”sql”]
CREATE VIEW WhatIsLocked
AS
SELECT
Locks.request_session_id AS SessionID,
Obj.Name AS LockedObjectName,
DATEDIFF(second,ActTra.Transaction_begin_time, GETDATE()) AS Duration,
ActTra.Transaction_begin_time,
COUNT(*) AS Locks
FROM    sys.dm_tran_locks Locks
JOIN sys.partitions Parti ON Parti.hobt_id = Locks.resource_associated_entity_id
JOIN sys.objects Obj ON Obj.object_id = Parti.object_id

JOIN sys.dm_exec_sessions ExeSess ON ExeSess.session_id = Locks.request_session_id
JOIN sys.dm_tran_session_transactions TranSess ON ExeSess.session_id = TranSess.session_id
JOIN sys.dm_tran_active_transactions ActTra ON TranSess.transaction_id = ActTra.transaction_id
WHERE   resource_database_id = db_id() AND Obj.Type = ‘U’
GROUP BY ActTra.Transaction_begin_time,Locks.request_session_id, Obj.Name
[/sourcecode]

You can use this view to keep automated watch. you can query this view every few minutes in database in business hours and if find lock bigger then certain duration, send email with detail. You can even keep watch by putting this query in web/windows application which keeps refreshing every few seconds and show details of locking.

I have written one more article in past which displays lock table, table with  ‘SCH-M’ lock and which command causing the lock. read article in detail, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations

Find fully qualified path in SQL Server 2008/2005

It has been observed so many times that when programmer creates table or stored procedure in SQL Server and when tries to run it with its name, they face an error that object doesn’t exists. If you are sure that you have created object even though it is not available even after you are having full permission in database than there is a case that, object owns by particular schema and you are not referring it while calling.
There are few schema in Adventureworks database and each owns few tables. If you try to use table name only in SELECT statement, you will face an error given below.
–try running below query
use AdventureWorks
go
select * from Address
–it will show you below given error
–Msg 208, Level 16, State 1, Line 1
–Invalid object name ‘Address’.
–reason is Address table is owned by Person schema
–so if you try like below, you are done.
select * from Person.Address
–Person.Address
–we have specified schema name and table name in above query
–but the good practise is to specify ServerName.DatabaseName.Schemaname.ObjectName
–this is called fully qualified path and to find out fully qualified path of object
–use below give T-SQL statement.
select ‘[‘+@@SERVERNAME+‘]’  + ‘.’ +DB_NAME() + ‘.’ +ss.name+‘.’+so.name as ‘Full Qualified Name’ from sys.objects so join sys.schemas ss
on so.schema_id=ss.schema_id where so.name=‘Address’



Enjoy!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Very useful Catalog View in SQL Server 2005:

Catalog View is used to get very crucial and important information about your database and server. There are lots of useful Catalog Views are available in Microsoft SQL Server 2005. Herewith, I am going to explain some of them.
Sys.Databases: this catalog view is useful to get information about all the available databases in sql server instance. It will provide you useful information like database name, database id, date of creation, compatibility level (database is compatible to which version of SQL Server, 90 is for SQL Server 2005), collation name (which language we can use in database) etc.
You can use following query for Sys.Databases:
Select * from sys.databases
GO
Sys.Database_files: this catalog view will give us information about particular database’s file. It will give you list of all files which have been used for database. You will get information about which type of files are there for particular database like LOG, DATA and FULLTEXT etc. Along with type of file, it will give you file name, its physical address on the server, state description whether it is ONLINE or OFFLINE, size of each file, growth rate of file etc.
You can use following query for Sys.Database_Files:
–return all files for Adventureworks database
USE AdventureWorks
SELECT * FROM sys.database_files
GO
Sys.Objects: This catalog will cater your need about all the objects available in database. You can get object name, its parent id(if there is any), type description like whether object is system table or user table or foreign key etc.
Below query will return all user tables:
USE AdventureWorks
SELECT * FROM Sys.Objects WHERE Type_Desc=‘User_Table’
GO
Sys.Key_Constraint: this catalog view will give you details about primary key in your database. You can get Parent name, constraint name, and schema id and type description by below query.
SELECT Object_Name(Parent_Object_Id) as ParentName,name,Schema_id,type_desc FROM sys.key_constraints
Sys.Foreign_Key_Columns: You will get information about available foreign key in your database along with its table name and referenced table name with following query.
SELECT Object_Name(Constraint_Object_Id) as ‘Name’,Object_Name(Parent_Object_Id) AS ‘TableName’,Object_Name(Referenced_Object_id) AS ‘Referenced Table’ FROM sys.foreign_key_columns
Sys.Columns: You will get information about all columns available in all tables for selected database. You will get Table name, column name, and collation name, null value status for column, maximum length and precision of columns by following query.
SELECT Object_Name(Object_Id) as ‘TableName’,Name as ‘ColumnName’,collation_Name,is_nullable,max_length,precision FROM Sys.Columns
There many more catalog views are available in SQL Server 2005, I will cover few more in my later article.
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah