Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Column and constraint Lookup Expand / Collapse
Author
Message
Posted Wednesday, March 25, 2009 10:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 4:24 PM
Points: 27, Visits: 122
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
Post #683482
Posted Wednesday, March 25, 2009 11:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #683507
Posted Wednesday, March 25, 2009 11:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 4:24 PM
Points: 27, Visits: 122
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.
Post #683525
Posted Wednesday, March 25, 2009 11:52 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 4,388, Visits: 9,510
Are you on SQL Server 2005?

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #683536
Posted Wednesday, March 25, 2009 11:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #683538
Posted Wednesday, March 25, 2009 12:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 4:24 PM
Points: 27, Visits: 122

Thank you Very much .I got it. I am in SQL Server 2005.
Post #683566
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse