Post reply

Pls Help ....Script to List the Database Objects With Specific Keyword

  • October 14, 2009 at 7:54 am

    #134505

    Pls Help ....

    Script to List the Database Objects With Specific Keyword

  • October 14, 2009 at 7:58 am

    #1065683

    all objects are in the system view sys.objects

    so you'd simply do SELECT * FROM sys.objects where name like'%BIZ%' for example.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 14, 2009 at 8:03 am

    #1065687

    And if you don't specifically need this to be a script, just hit F4 in your query analyzer to do an object search. If I didn't need my F4 button for other apps, I'd have ripped that key off years ago and left it sitting in the drawer next to my insert key.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 14, 2009 at 8:03 am

    #1065690

    Thanks ..

    If you don't mind .. Can you tell me

    Script to List of the Objects that uses a Specific Column name

  • October 14, 2009 at 8:11 am

    #1065702

    I can use this .. Right

    But the Problems is .. Gives Error in 2000

    SELECT Name, create_date,modify_date

    FROM sys.procedures

    WHERE OBJECT_DEFINITION(object_id) LIKE '%CDRMaster%'

    AND OBJECT_DEFINITION(object_id) LIKE '%CDRID%'

  • October 14, 2009 at 8:12 am

    #1065704

    John Paul-702936 (10/14/2009)

    Thanks ..

    If you don't mind .. Can you tell me

    Script to List of the Objects that uses a Specific Column name

    aain, you'd use one of the system views, thistime sys.columns

    SELECT OBJECT_NAME(object_id) AS TableName ,name as ColumnName from sys.columns where name='ORDERID'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 14, 2009 at 8:15 am

    #1065706

    Does n't works in 2000...

    What is equivalent to OBJECT_name in 2000

  • October 14, 2009 at 8:23 am

    #1065713

    you posted in a SQL 2005 forum, so you got 2005-specific examples.

    it's not object_name that is stopping your script...it's sys.columns:

    SELECT OBJECT_NAME(id) from syscolumns where name ='ORDERID'

    will work in 2000

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 14, 2009 at 8:36 am

    #1065726

    It gives the table list which uses the Specific Column ..

    But , It's not Listed any storeprocedures or Function or Views ..

    Is anything else we need to add ... to get alist of SP,Fun,Vw

  • October 14, 2009 at 8:59 am

    #1065747

    I guess you are looking for this?

    Select * from sysobjects

    Where id in (Select id from

    syscomments Where text like '%urcolumn%')

    not sure if it works on 2000 as i dont use it, know it and i cant check it! 🙂

    ---------------------------------------------------------------------------------

  • October 14, 2009 at 9:52 am

    #1065799

    So you're basically looking for column level dependencies. In 2000, I don't think you're going to find anything. Someone might have a script that'd get you closer than this, but this will get you started:

    SELECT TOP 500 o.name, c.*

    FROM syscomments c

    INNER JOIN sysobjects o ON c.id = o.id

    WHERE c.text LIKE '% SCP %'

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 14, 2009 at 9:53 am

    #1065802

    Oops, just noticed PP posted the same thing :Wow:

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 14, 2009 at 10:21 am

    #1065827

    Thanks for Script ..

    I am very sorry to Bug you again and agian .. Actually I don't have Clear idea what i need Extractly ..

    Please Can You tell like

    How to find dependencies of a Varibale

    Like I have varibale ' @a '.. Is there any Script to get the list of the Objects that are using Specific varibale '@A'

    I tried to use the Previous Script .. But it gives all the Objects which uses varibales like .. ' @AA ' , @art.. Because of the ' % ' we are using ... If i excute Without ' %'.. no objects are displyed ...

    Can you help me out pls :

    GET THE LIST OF OBJECTS WHICH USES SPECIFIC @VARIBALE ?

  • October 14, 2009 at 10:23 am

    #1065828

    Use '%@A %' instead of '%@A%'. (There's a space after the A in the first one)

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 14, 2009 at 10:30 am

    #1065833

    Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply