Get depended object from SP_Depends and Information_Schema.routines in SQL Server 2008

SP_Depends is very useful stored procedure which can give you list of the dependent object of your table or views. If you pass table name in SP_Depends, it will give you depended views, stored procedures, functions etc. let us look at this by small demo.

–create one small database for testing

create database DependCheck

go

use DependCheck;

go

–create one table and insert some data

create table TestDepend

(

ID Int Identity(1,1),

Name Varchar(20)

)

GO

Insert into TestDepend

SELECT ‘RITESH’ UNION ALL

SELECT ‘RAJAN’ UNION ALL

SELECT ‘ALKA’

go

–create one PROC which is depend on TestDepend Table

CREATE PROC uspTestDependSelectAll

AS

SELECT * FROM TestDepend

GO

–try executing PROC

EXEC uspTestDependSelectAll

GO

–check SP_Depends system procedure to get all dependent objects

sp_depends ‘TestDepend’

GO

–here is one alternate way to get dependent list

SELECT * FROM information_schema.routines ISR WHERE charindex(‘TestDepend’, ISR.ROUTINE_DEFINITION)>0

GO

–USE master;

–GO

–DROP DATABASE DEPENDCHECK

–GO

Well we have seen two different ways to get list of dependent objects of tables but in my live database when I run both way, it gives me different results however in our case it will give same results. So the question I would like to ask is, which way is correct and reliable?

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

Advertisements

Get depended object from SP_Depends and Information_Schema.routines in SQL Server 2008

SP_Depends is very useful stored procedure which can give you list of the dependent object of your table or views. If you pass table name in SP_Depends, it will give you depended views, stored procedures, functions etc. let us look at this by small demo.

–create one small database for testing

create database DependCheck

go

use DependCheck;

go

–create one table and insert some data

create table TestDepend

(

ID Int Identity(1,1),

Name Varchar(20)

)

GO

Insert into TestDepend

SELECT ‘RITESH’ UNION ALL

SELECT ‘RAJAN’ UNION ALL

SELECT ‘ALKA’

go

–create one PROC which is depend on TestDepend Table

CREATE PROC uspTestDependSelectAll

AS

SELECT * FROM TestDepend

GO

–try executing PROC

EXEC uspTestDependSelectAll

GO

–check SP_Depends system procedure to get all dependent objects

sp_depends ‘TestDepend’

GO

–here is one alternate way to get dependent list

SELECT * FROM information_schema.routines ISR WHERE charindex(‘TestDepend’, ISR.ROUTINE_DEFINITION)>0

GO

–USE master;

–GO

–DROP DATABASE DEPENDCHECK

–GO

Well we have seen two different ways to get list of dependent objects of tables but in my live database when I run both way, it gives me different results however in our case it will give same results. So the question I would like to ask is, which way is correct and reliable?

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

Interesting observation about VIEW and ORDER BY clause in SQL Server 2008

View is been always interesting topic for me and for many of the SQL professionals. Use of ORDER BY clause inside the view is really a question of debate since long. I am going to present my observation about view in this article which I have tested in SQL Server 2008 (10.0.1600.22 ((SQL_PreRelease).080709-1414 ))

It is not guaranteed that it will remain same in all versions of SQL Server.

Basically, you can’t create VIEW with ORDER BY clause. However, if you will use TOP clause inside your SELECT clause, you can specify ORDER BY clause.

NOTE: why Order by is not working with VIEW and why it is working with TOP clause is also an interesting topic which I will discuss sometime later.

Anyway, let us go through some small practical code snippet which is really basic but useful to make fundamental clear.

CREATE TABLE ViewTest

(

      col1 CHAR(1)

)

GO

INSERT INTO ViewTest

SELECT ‘A’ UNION ALL

SELECT ‘C’ UNION ALL

SELECT ‘D’ UNION ALL

SELECT ‘B’ UNION ALL

SELECT ‘Z’ UNION ALL

SELECT ‘R’

GO

select Col1 from ViewTest order by col1

GO

–Result of above query

–Col1

——

–A

–B

–C

–D

–R

–Z

–(6 row(s) affected)

CREATE VIEW vViewTest AS

select Col1 from ViewTest order by col1

GO

–creation of this view won’t work rather,

–you will be greeted with following error

–Msg 1033, Level 15, State 1, Procedure vViewTest, Line 2

–The ORDER BY clause is invalid in views, inline functions, derived tables,

–subqueries, and common table expressions, unless TOP or FOR XML is also specified.

–you can’t use ORDER BY clause in View that’s why you faced an error.

–even if you will try use TOP clause, you can specify ORDER BY

CREATE VIEW vViewTest AS

select TOP 100 PERCENT Col1 from ViewTest order by col1

GO

SELECT * FROM vViewTest

GO

–result of above query

–Col1

——

–A

–C

–D

–B

–Z

–R

–(6 row(s) affected)

Result is not sorted, however it was working in SQL Server 2000 but right now it is not working in SQL Server 2005+ versions.

Let us try one more thing here.

ALTER VIEW vViewTest AS

select TOP 99.999 PERCENT Col1 from ViewTest order by col1

GO

SELECT * FROM vViewTest

GO

–result of above query is.

–Col1

——

–A

–B

–C

–D

–R

–Z

–(6 row(s) affected)

Bingo!!! it is working now but this is I guess error or something so I recommend not to trust this 99.999 stuff and simply use following solution for ordering your view. If you right now use “99.999” stuff and in next patches or in next fixes, it might stop working but if you will ordering your result set while executing select, it is guaranteed that ordering will work 100% sure. For eg:

ALTER VIEW vViewTest AS

select Col1 from ViewTest

GO

SELECT * FROM vViewTest order by col1

GO

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

Interesting observation about VIEW and ORDER BY clause in SQL Server 2008

View is been always interesting topic for me and for many of the SQL professionals. Use of ORDER BY clause inside the view is really a question of debate since long. I am going to present my observation about view in this article which I have tested in SQL Server 2008 (10.0.1600.22 ((SQL_PreRelease).080709-1414 ))

It is not guaranteed that it will remain same in all versions of SQL Server.

Basically, you can’t create VIEW with ORDER BY clause. However, if you will use TOP clause inside your SELECT clause, you can specify ORDER BY clause.

NOTE: why Order by is not working with VIEW and why it is working with TOP clause is also an interesting topic which I will discuss sometime later.

Anyway, let us go through some small practical code snippet which is really basic but useful to make fundamental clear.

CREATE TABLE ViewTest

(

      col1 CHAR(1)

)

GO

INSERT INTO ViewTest

SELECT ‘A’ UNION ALL

SELECT ‘C’ UNION ALL

SELECT ‘D’ UNION ALL

SELECT ‘B’ UNION ALL

SELECT ‘Z’ UNION ALL

SELECT ‘R’

GO

select Col1 from ViewTest order by col1

GO

–Result of above query

–Col1

——

–A

–B

–C

–D

–R

–Z

–(6 row(s) affected)

CREATE VIEW vViewTest AS

select Col1 from ViewTest order by col1

GO

–creation of this view won’t work rather,

–you will be greeted with following error

–Msg 1033, Level 15, State 1, Procedure vViewTest, Line 2

–The ORDER BY clause is invalid in views, inline functions, derived tables,

–subqueries, and common table expressions, unless TOP or FOR XML is also specified.

–you can’t use ORDER BY clause in View that’s why you faced an error.

–even if you will try use TOP clause, you can specify ORDER BY

CREATE VIEW vViewTest AS

select TOP 100 PERCENT Col1 from ViewTest order by col1

GO

SELECT * FROM vViewTest

GO

–result of above query

–Col1

——

–A

–C

–D

–B

–Z

–R

–(6 row(s) affected)

Result is not sorted, however it was working in SQL Server 2000 but right now it is not working in SQL Server 2005+ versions.

Let us try one more thing here.

ALTER VIEW vViewTest AS

select TOP 99.999 PERCENT Col1 from ViewTest order by col1

GO

SELECT * FROM vViewTest

GO

–result of above query is.

–Col1

——

–A

–B

–C

–D

–R

–Z

–(6 row(s) affected)

Bingo!!! it is working now but this is I guess error or something so I recommend not to trust this 99.999 stuff and simply use following solution for ordering your view. If you right now use “99.999” stuff and in next patches or in next fixes, it might stop working but if you will ordering your result set while executing select, it is guaranteed that ordering will work 100% sure. For eg:

ALTER VIEW vViewTest AS

select Col1 from ViewTest

GO

SELECT * FROM vViewTest order by col1

GO

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

Find Specific word or phrase from all stored procedure, views, and triggers available in SQL Server 2005/2008

We may require sometime to find specific TableName or some words or some phrase have been used in any SP, Triggers or in VIEW. This is interesting to know how we can do it with T-SQL rather than manually checking script of everything.

Before I move forward to my original script, let me give you some basic logic behind that. Actually SYSObjects contains Name, ID along with many useful information of objects like stored procedure, view, trigger, function, user table, system table etc and SysComments contain ID of objects along with Text which has been used to create that object. So, now it may have popped up in your mind that I must have joined these two system views.

Before I show you the script I also would like to introduce you with the possible values could be used in “XType” column of “SysObjects” as that is the base and you can decide what to look (SP, Triggers, Views or anything else)

Here are some of the values of “Xtype” column and its meaning.

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

So now you are all set to look at the script, have a look at it.

–display the list of any trigger, view and stored procedure which has used ‘SY’ in the script

SELECT DISTINCT so.name,sc.text

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE so.xtype in (‘P’,‘TR’,‘V’) and sc.TEXT LIKE ‘%sy%’

order by name

 

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