Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Quering Code Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 8:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 316, Visits: 550
Is there anyway to query all your Stored Proc's, Views, and functions? I need to check to see what code is using a table.
Post #1452134
Posted Monday, May 13, 2013 8:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
Install RedGate DB search - it's free!

http://www.red-gate.com/products/sql-development/sql-search/


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1452142
Posted Monday, May 13, 2013 8:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:29 AM
Points: 368, Visits: 1,232
SELECT DISTINCT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
ON d.object_id = o.object_id
INNER JOIN sys.objects p
ON d.referenced_major_id = p.object_id

Worth a try from a quick google search
Post #1452144
Posted Monday, May 13, 2013 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
Eugene Elutin (5/13/2013)
Install RedGate DB search - it's free!

http://www.red-gate.com/products/sql-development/sql-search/


+1

Once you install it you will ask yourself how you ever managed to find anything without it. I use it daily.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1452147
Posted Monday, May 13, 2013 8:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
michael.higgins (5/13/2013)
SELECT DISTINCT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
ON d.object_id = o.object_id
INNER JOIN sys.objects p
ON d.referenced_major_id = p.object_id

Worth a try from a quick google search


sql_dependencies is not reliable source for determining if some table is used in views or programming objects...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1452151
Posted Monday, May 13, 2013 8:46 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:29 AM
Points: 368, Visits: 1,232
Ah, thanks for that :) M
Post #1452152
Posted Monday, May 13, 2013 8:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 23,081, Visits: 31,616
Eugene Elutin (5/13/2013)
michael.higgins (5/13/2013)
SELECT DISTINCT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
ON d.object_id = o.object_id
INNER JOIN sys.objects p
ON d.referenced_major_id = p.object_id

Worth a try from a quick google search


sql_dependencies is not reliable source for determining if some table is used in views or programming objects...


But it is getting better with each release. Not sure how good it as this time, but I know it didn't catch forward dependencies in some of the earlier versions of SQL Server (like SQL Server 2000 iirc).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1452156
Posted Monday, May 13, 2013 10:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 316, Visits: 550
I do not have permissions on the server.. and there is a lot of red tape to get anything installed.

However I did use the SQL provided... with a few changes... thanks for everyones help.

; with base as
(
SELECT DISTINCT o.name as Calling_Name
, o.type_desc as Calling_desc
, p.name as Called_Name
, p.type_desc as Called_Desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
ON d.object_id = o.object_id
INNER JOIN sys.objects p
ON d.referenced_major_id = p.object_id
)
select * from base
where [called_name] like 'zt_MSC%'
order by called_name, Calling_name
Post #1452210
Posted Monday, May 13, 2013 10:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
dwilliscp (5/13/2013)
I do not have permissions on the server.. and there is a lot of red tape to get anything installed.

However I did use the SQL provided... with a few changes... thanks for everyones help.

; with base as
(
SELECT DISTINCT o.name as Calling_Name
, o.type_desc as Calling_desc
, p.name as Called_Name
, p.type_desc as Called_Desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
ON d.object_id = o.object_id
INNER JOIN sys.objects p
ON d.referenced_major_id = p.object_id
)
select * from base
where [called_name] like 'zt_MSC%'
order by called_name, Calling_name


You don't need to install RedGate search on a server. It's SSMS add-in and you should install it on on your desktop/vm. It's one of the most used SQL development tools, provided for free by one of the most known software companies specializing in this area.
As I said before sys.sql_dependencies may not contain data for all dependent objects.
If you go this way, you better to check syscomments too.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1452225
Posted Monday, May 13, 2013 5:01 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 1,975, Visits: 2,923
I also search table sys.sql_modules, which contains the code for all sys.objects members of type P, RF, V, TR, FN, IF, TF, and R.

If you want to check for use in a job step, also check table sysjobsteps (msdb.dbo.sysjobsteps), column "command".


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1452359
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse