Solved

Where_Am_I  Script

Posted on 2004-09-22
5
299 Views
Last Modified: 2012-06-22
I used to have a script that I ran in Query Analyzer called Where_Am_I.  It would allow me to search a database for any reference to string that I passed as a parameter.  i.e. Where_Am_I  'UserName'  might return a list of all the tables that contain a field called UserName and all stored procedures that have a reference to this field.

I was hoping someone might have a sql script like that they can provide.

Thanks.
0
Comment
Question by:mtgcpc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 1

Expert Comment

by:E-Squared
ID: 12128098
CREATE PROCEDURE FindObjects(@KeyWord as varchar(100))
AS
SELECT
      *
      FROM sysobjects O
            INNER JOIN syscomments C on O.ID = C.ID
      WHERE C.text like '%' + @KeyWord + '%'

GO

FindObjects 'YourStringToFind'

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

This method has the flaw that any token which is split by an 8000-byte boundary will not be found. Do you want a version that does not have this limitation?
0
 
LVL 1

Accepted Solution

by:
SirParadox earned 125 total points
ID: 12128855
I have two Procedures I use regulary tl and sl
 tl for table lookup
 sl for procedure - function - trigger search

tl 'IpAddresses'
Sl 'SCOPE_IDENTITY()'

...


CREATE PROCEDURE sl
        @text varchar(64)
      , @Notcontain1 varchar(255) = NULL
      , @Notcontain2 varchar(255) = NULL
      , @Contain1 varchar(255) = NULL
      , @Contain2 varchar(255) = NULL
AS

SELECT 'SELECT text FROM syscomments WHERE ID=' + cast(sysobjects.ID as varchar) + space(10-len(sysobjects.ID)) + ' -- ' + xtype + ' ' + sysobjects.name FROM sysobjects WHERE ID IN (SELECT DISTINCT ID FROM syscomments WHERE
      TEXT like '%' + @text + '%'
            AND (@Notcontain1 IS NULL OR text not like '%' + @Notcontain1 + '%'  )
            AND (@Notcontain2 IS NULL OR text not like '%' + @Notcontain2 + '%'  )
            AND (@Contain1 IS NULL OR text  like '%' + @Contain1 + '%'  )
            AND (@Contain2 IS NULL OR text  like '%' + @Contain2 + '%'  )
      AND Name Not Like 'zzz%' )
ORDER BY sysobjects.Xtype, sysobjects.name
GO

CREATE PROCEDURE tl
@table varchar(64)
AS
      SELECT 'SELECT * FROM ' + cast(name as varchar(128)) from sysobjects where xtype='U' and (name like '%' + @table + '%') ORDER BY NAME
GO
0
 
LVL 1

Expert Comment

by:SirParadox
ID: 12375352
I'm still here, but I guess the Asker is awol
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question