Column and constraint Lookup

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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