June 23, 2011 at 1:26 am
Hi,
I am in middle of a huge replication project. My database has many tables which do not have primary keys(migrated from MS Access 10 years ago). Now, we have decided to create an identity key as primary key to these tables. The developers who are in the company for long time say that this will be okay but I cannot take a chance and wish to verify all the stored procedure and functions which use that table.
I can right click the table and see object dependencies. Is this a full proof method or is there a neat script to find the same. I did find some scripts over mentioned in msdn forms but it failed to show procedure which does have my table but without the schema being explicitly mentioned i.e the procedure had my table written as 'table_name' rather than 'dbo.table_name', so the script is not full proof.
Any suggestions here.
Thanks
Chandan
June 23, 2011 at 1:39 am
I typically use SQLSearch from Idera's SQLAdminToolset suite since I own the suite already. This searches all stored procs, triggers, etc.
There are other tools from other vendors, as well as free scripts as you mentioned. Hopefully someone here can recommend a good script that actually finds all references and not just some of them.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
June 23, 2011 at 4:39 am
Any suggestions from anyone. I am sure someone must have faced this question atleast once in their career 🙂
June 23, 2011 at 4:48 am
The sys.sql_modules system view contains the full definitions for all functions and procedures. You can look for each table name in the command or definition column (I can't remember what it's called).
John
June 23, 2011 at 4:57 am
You can query the sys.sysdepends view, like:
SELECT object_name(id) as MainObj, object_name(depid) as DependObj FROM sys.sysdepends
... you can join sys.sysobjects view to get more info, like:
SELECT object_name(d.id) as MainObj, o1.type 'MainObjType', -- P: proc
object_name(d.depid) as DependObj, o2.type 'DependObjType' -- U: Table
FROM sys.sysdepends d
join sys.sysobjects o1
on d.id = o1.id
join sys.sysobjects o2
on d.depid = o2.id
June 23, 2011 at 5:29 am
I am not sure if sysdepends is a fool proof way of identifying dependencies.
I remember i could create procedures to select from non existent tables and then add the tables later and not have a dependency between the table and the proc but I think this was in sql 2005 ,I am not sure if the same applies to SQL 2008.
June 23, 2011 at 5:41 am
No Jayanth_Kurup,
You can compile your stored procedure with non-existent tables but for the same there is no records being maintained in sys.sql_dependencies. So the above solution is pretty close, I also think in the same way.
Regards.
June 23, 2011 at 6:03 am
Have you ever come across an informational message
"Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'XXXXXX. The stored procedure
will still be created"
http://www.nyx.net/~bwunder/dbChangeControl/sp_depends.htm
The above link gives a good example of why i think sysdepends is not reliable.
June 23, 2011 at 6:16 am
Hi,
select 'sp_depends '+''''+table_schema+'.'+table_name+'''' from information_schema.tables
regards
Siva Kumar J.
June 23, 2011 at 6:48 am
When i right click on the table and see dependencies, it shows me stored procedures or views depending on it. Is it a correct and reliable way?
June 23, 2011 at 7:56 am
chandan_jha18 (6/23/2011)
When i right click on the table and see dependencies, it shows me stored procedures or views depending on it. Is it a correct and reliable way?
it's pretty close except for some of the more rare "edge" cases, which is why some folks are stating it's not 100% reliable.
if you have any procs using dynamic SQL that substitutes a tablename, or, as stated before, a proc that was created before the tables it references exists, those items will not have any dependancies...but everything else will.
it will be 100% accurate for tables with foreign keys, all views and all functions, as they cannot be created with a reference to a non-existant object.
Lowell
June 23, 2011 at 10:57 am
You might as well use below query to find any sproc that uses your table.
select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%YourTableName%'
June 23, 2011 at 8:37 pm
Redgate has a very nice little utility called SQLSearch.
It's very handy for finding strings (like table names) within all objects in a db (or all dbs on a server). It can catch things like table names in dynamic SQL.
It is available as a free download here[/url].
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 23, 2011 at 10:45 pm
Hi Jayanth,
Sorry for late reply. Yes I do agree with you, sysdepends is not reliable because it does not maintain the changes in definitions effectively. But it can help you with following trick:
1. Generate script from existing database.
2. Create/execute on another test/development server.
3. Run sys.sysdepends as described by manub22.
Chandan, You can see dependency like that as well. But, you should try the sys.sysdepends also as discussed.
Regards,
Sudhir
June 24, 2011 at 10:32 am
The Dixie Flatline (6/23/2011)
Redgate has a very nice little utility called SQLSearch.It's very handy for finding strings (like table names) within all objects in a db (or all dbs on a server). It can catch things like table names in dynamic SQL.
Oh cool. I thought it was still only in the SQLAdminToolset. I recommended this tool as the first response to this thread, but I did NOT know it was [now] free.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply