Column and constraint Lookup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Column and constraint Lookup

Column and constraint Lookup

Rate Topic

Display Mode

Topic Options

Author

Message

Nilesh-788004

Posted Wednesday, March 25, 2009 10:56 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:33 PM
Points: 24, Visits: 106

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

Florian Reischl

Posted Wednesday, March 25, 2009 11:23 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 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

Nilesh-788004

Posted Wednesday, March 25, 2009 11:45 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:33 PM
Points: 24, Visits: 106

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

Jeffrey Williams 3188

Posted Wednesday, March 25, 2009 11:52 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216

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

Florian Reischl

Posted Wednesday, March 25, 2009 11:57 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 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

Post #683538

Nilesh-788004

Posted Wednesday, March 25, 2009 12:20 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:33 PM
Points: 24, Visits: 106

Thank you Very much .I got it. I am in SQL Server 2005.

Post #683566

« Prev Topic | Next Topic »

Permissions