SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 VIEW - TableReferences
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 02/21/2002 :  16:12:33  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
CREATE VIEW TableReferences AS
SELECT TOP 100 PERCENT
t.TABLE_NAME,
OBJECT_NAME(c.id) AS [OBJECT_NAME],
COUNT(*) [MATCHES]
FROM INFORMATION_SCHEMA.TABLES t,
syscomments c
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND c.text LIKE '%' + t.TABLE_NAME + '%'
GROUP BY t.TABLE_NAME,
c.id
ORDER BY t.TABLE_NAME, [MATCHES] DESC
GO

Sample Usage:
SELECT * FROM TableReferences WHERE TABLE_NAME = 'MyTable'


Edited by - onamuji on 02/21/2002 16:13:27

Spyder
SQLTeam Author

USA
75 Posts

Posted - 02/22/2002 :  02:43:30  Show Profile  Visit Spyder's Homepage  Reply with Quote
Hey, thanks for posting that; looks like that will come in handy!

Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 02/22/2002 :  08:49:56  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
Ya ... had to add a reference to a table ... needed a way to find all it's references in the procedures ... made it VERY easy to find them all ... :-) I bet this could be changed to find references of VIEWS, FUNCTIONS, and PROCEDURES within other functions/procedures ...

- Onamuji
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000