June 26, 2008 at 12:53 pm
Is there a way to search for all instances of a specific table in SQL? Meaning if I wanted to delete a table but it was used in say a view, is there a way to search.
June 26, 2008 at 2:41 pm
You can check sysdepends, but that's not always accurate. You can also search syscomments, but that may or may not work.
Red Gate (I work for them) has a dependency tracker tool that can tell you this.
June 26, 2008 at 3:02 pm
where could i find this tracker?
July 4, 2008 at 10:47 am
Visit http://www.red-gate.com/
Extremely cool product suite.
July 4, 2008 at 10:51 am
Alternatively...
1. Run a profiler trace against your database and then run through looking for any calls to this table. The downside to this is that the call to a particular table is not guaranteed to happen whilst you run the trace. For example a table may only be used for month end processing.
2. Script the entire database out and do a manual text search on the table name
3. Restore the database in test, rename the object, test and wait for things to go bang! Again, like the profiler trace, no guarantee in finding the issue if every single function of the app is not tested.
July 6, 2008 at 10:33 am
Hi
Method 1 - INFORMATION_SCHEMA.ROUTINES
This approach uses INFORMATION_SCHEMA.ROUTINES to search through the definition of the routine such as the stored procedure, trigger, etc...
Method 2 - sp_depends
This approach uses the system stored procedure sp_depends.
Method 3 - Using syscomments
This approach reads data from the syscomments table. This is similar to method 1.
Method 4 - sp_MSdependencies
This approach uses the system stored procedure sp_MSdependencies.
As you can see from these different methods each gives you a different part of the answer. So to be safe none of these approaches is full proof. To get a complete listing you really need to employ a few different methods to ensure you are not missing anything.
Check out the below link
http://www.mssqltips.com/tip.asp?tip=1294
Thanks -- Vj
July 7, 2008 at 7:29 am
very cool thank you to everyone for there help, I am very much a novice at this stuff so will take me a while to figure some of this stuff out but at least i'm pointed in the right direction.
Thanks
July 7, 2008 at 4:28 pm
In addition to what the others have told you add this aide:
The following will find all views in a database containing the specified text (that is the table name you are searching for) contained in the text that created the view definition. Included in that text is the T-SQL statement which is part of the view definition.
SELECT so.name AS 'View Name',sc.text AS 'T-SQL' FROM Sysobjects so, syscomments sc
where so.xtype = 'V' and sc.id = so.id AND sc.text LIKE '%insert your table name here%' ORDER By sc.colid
My table name is logentries and here is my result:
View Name
LogEntries_VIEW
T- SQL
CREATE VIEW [LogEntries_VIEW]
AS SELECT [dbo].[LogEntries].[EntryId], [dbo].[LogEntries].[DateEntered] FROM [dbo].[LogEntries]
For further information refer to BOL syscomments system table.
Further the text you are searching for does not need to be a table name, it could be a column name another view name etc., etc.
To check for triggers which contain the table name being searched for use:
SELECT so.name AS 'Trigger Name',sc.text AS 'T-SQL' FROM Sysobjects so, syscomments sc
where so.xtype = 'TR' and sc.id = so.id AND sc.text LIKE '%logentries%'
ORDER By sc.colid
and this will perform the same checking as the one above for views.
Note: The important item to change is so.xtype from 'V' to 'TR'
July 8, 2008 at 6:31 am
Wow thank you that is exactly what I am looking for. Thanks to everyone for there help, this website is a valuable asset to a rookie like me. Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply