March 25, 2009 at 10:56 am
Hi,
Iam trying to figure out how to find a column in Object Explorer or in a list of Stored Procedures (Object Explorer) and a string in database especially when one is working with 400-500 tables and 100's of stored Procedures.
In Sybase and Oracle ( Toad) it is easy to find such info .But in SQL Server One has to go to Menu Edit-Find-FInd inFiles. If you Stored Procedures are on Desktop . i know sysobjects.But I am not able to get the right info.
Is there a system stored Procedure or a script
For example,
I want to find Column DealerPriorityID in a stored Porcedures and in a table and a particulsr word say 'Chain Saw' in SQL Server 2005.
Is there a script
thank you
March 25, 2009 at 11:23 am
Hi
To find out which object is referenced by any other use:
SELECT OBJECT_NAME(referencing_id) referencing,
OBJECT_NAME(referenced_id) referenced,
*
FROM sys.sql_expression_dependencies
To find out which procedure uses "CustomerId" use:
SELECT OBJECT_NAME(object_id), *
FROM sys.all_sql_modules
WHERE definition LIKE '%CustomerId%'
To find out which table contains a special word (within its data) there is no build in function.
Greets
Flo
March 25, 2009 at 11:45 am
Thanks for your reply.But the 1st query gives me an error in Adventure Works DB:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sql_expression_dependencies'.
So I tried to see if there is a spelling mistake in 'sys.sql_expression_dependencies'.
I typed .sql_expression_dependencies' in google and found this link
http://msdn.microsoft.com/en-us/library/bb677315.aspx
and copy pasted
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,
o.type_desc AS referencing_desciption,
COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,
referencing_class_desc, referenced_class_desc,
referenced_server_name, referenced_database_name, referenced_schema_name,
referenced_entity_name,
COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription');
.Still it gives me the same error irrespective of any Database. This time I tried in Northwinds.
SAme error.
March 25, 2009 at 11:52 am
Are you on SQL Server 2005?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 25, 2009 at 11:57 am
Oups... sorry! I just tried on my SQL Server 2008.
Just researched... so this should work on SQL Server 2005:
SELECT OBJECT_NAME(object_id) referencing,
OBJECT_NAME(referenced_major_id),
*
FROM sys.sql_dependencies
Greets
Flo
March 25, 2009 at 12:20 pm
Thank you Very much .I got it. I am in SQL Server 2005.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply