May 13, 2013 at 8:30 am
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.
May 13, 2013 at 8:36 am
Install RedGate DB search - it's free!
http://www.red-gate.com/products/sql-development/sql-search/
May 13, 2013 at 8:38 am
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
May 13, 2013 at 8:40 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2013 at 8:45 am
michael.higgins (5/13/2013)
SELECT DISTINCT o.name, o.type_desc, p.name, p.type_descFROM 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...
May 13, 2013 at 8:46 am
Ah, thanks for that 🙂 M
May 13, 2013 at 8:54 am
Eugene Elutin (5/13/2013)
michael.higgins (5/13/2013)
SELECT DISTINCT o.name, o.type_desc, p.name, p.type_descFROM 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).
May 13, 2013 at 10:30 am
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
May 13, 2013 at 10:56 am
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.
May 13, 2013 at 5:01 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
May 13, 2013 at 5:34 pm
ScottPletcher (5/13/2013)
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".
+1, except for the job part. I use this constantly, particularly when I'm trapsing around in other architectures trying to figure out what the... errr... /rant. sys.sql_modules, what did I ever do without you?... oh, right, play games in syscomments trying to concatonate the mess.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 13, 2013 at 7:31 pm
Sean Lange (5/13/2013)
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.
Ya just gotta like Microsoft. Such functionality used to be available in SQL Server 2000 by pressing the {f4} key. It was a great, fully documented feature and they simply took it away with the release of SQL Server 2005. I'd love to meet the person that made that decision... there would certainly be a load of high velocity porkchops in that meeting.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2013 at 7:38 pm
I do the same thing as what Scott identified above. I use sys.sql_modules and other system tables to help me on such tasks. Just be aware that comments will frequently give false returns when searching for common names like "Client" or "User(s)" or "Employee(s)", etc.
Heh... but, not to worry. According to a lot of the code I've seen, comments won't interfere because (unfortunately) so few people actually use them. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2013 at 7:41 pm
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 could be missing out on quite a bit. Since stored procedures can be built before the objects they work with are built, you could have a whole lot of stored procedures where the dependencies were never updated.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 14, 2013 at 9:59 am
Thanks for the warning Jeff.
On the Jobs front.. We almost never put SQL into the Job, but thanks for reminding me to check there too. Thanks to everyone for your help in building a SQL statment. Oh, and I do not have access on my PC to install ... but I put in a request to get the Redgate plug-in installed on my PC.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply