SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 The way to find all references to the table column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kasper
Starting Member

7 Posts

Posted - 11/21/2006 :  15:47:39  Show Profile  Reply with Quote
Hi,
Is there a way to find all database objects(icluding the stored procedures, views, etc.), that have references to the specified column of the specified table.

Thanks.

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/21/2006 :  22:04:10  Show Profile  Reply with Quote
One way (that will take a little effort) is the following statements. It could give you some false positives if your column is a common name like [code].

use pubs
select distinct routine_name, routine_type as object from information_schema.routines where routine_definition like '%pub_id%'
union
select table_name, 'table/view' as object from information_schema.columns where column_name = 'pub_id'

Be One with the Optimizer
TG
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 11/21/2006 :  22:56:08  Show Profile  Visit jezemine's Homepage  Reply with Quote
for something like this I build a data dictionary and then search it for the keyword of interest - it's foolproof and requires no real effort :)

sqlspec has a trial version (no expiration) that will do this for you, link in my sig below. it builds a chm that is searchable by any keyword.


SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/21/2006 :  23:57:01  Show Profile  Reply with Quote
quote:
Originally posted by TG

One way (that will take a little effort) is the following statements. It could give you some false positives if your column is a common name like [code].

use pubs
select distinct routine_name, routine_type as object from information_schema.routines where routine_definition like '%pub_id%'
union
select table_name, 'table/view' as object from information_schema.columns where column_name = 'pub_id'

Be One with the Optimizer
TG



information_schema.routines routine_definition only shows the first 4000 charactere of the procedure or function code, and does not include trigger code or view definitions at all. I think it is better to do a direct search of syscomments.

If you are only dealing with one database, just script all objects out to a single file, and search that in an editor.







CODO ERGO SUM

Edited by - Michael Valentine Jones on 11/21/2006 23:57:56
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 11/22/2006 :  04:30:25  Show Profile  Reply with Quote
try it

SELECT
'TABLE ' AS ObjectType,
TABLE_NAME AS ObjectName,
ORDINAL_POSITION AS ObjectOrder,
COLUMN_NAME AS ObjFieldName ,
DATA_TYPE as ObjDataType,
CASE DATA_TYPE
WHEN 'varchar' THEN CONVERT(varchar(4),CHARACTER_MAXIMUM_LENGTH)
WHEN 'nvarchar' THEN CONVERT(varchar(4),CHARACTER_MAXIMUM_LENGTH)
WHEN 'decimal' THEN CONVERT(varchar(2),NUMERIC_PRECISION)+ ', ' + CONVERT(varchar(2),NUMERIC_SCALE)
ELSE 'Default'
END AS ObjFieldLength
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME IN
(
select [name] from sysobjects where xtype = 'U'
)

UNION ALL

SELECT
'STORED PROC ' AS ObjectType,
SPECIFIC_NAME AS ObjectName,
ORDINAL_POSITION AS ObjectOrder,
PARAMETER_NAME AS ObjFieldName,
DATA_TYPE as ObjDataType,
CASE DATA_TYPE
WHEN 'varchar' THEN CONVERT(varchar(4),CHARACTER_MAXIMUM_LENGTH)
WHEN 'nvarchar' THEN CONVERT(varchar(4),CHARACTER_MAXIMUM_LENGTH)
WHEN 'decimal' THEN CONVERT(varchar(2),NUMERIC_PRECISION)+ ', ' + CONVERT(varchar(2),NUMERIC_SCALE)
ELSE 'Default'
END AS ObjFieldLength
FROM
INFORMATION_SCHEMA.PARAMETERS
WHERE
SPECIFIC_NAME NOT IN
(
select [name] from sysobjects where xtype = 'p' and status < 0
)
ORDER BY 1,2,3


Mahesh

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/22/2006 :  08:04:57  Show Profile  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by TG

One way (that will take a little effort) is the following statements. It could give you some false positives if your column is a common name like [code].

use pubs
select distinct routine_name, routine_type as object from information_schema.routines where routine_definition like '%pub_id%'
union
select table_name, 'table/view' as object from information_schema.columns where column_name = 'pub_id'

Be One with the Optimizer
TG



information_schema.routines routine_definition only shows the first 4000 charactere of the procedure or function code, and does not include trigger code or view definitions at all. I think it is better to do a direct search of syscomments.

If you are only dealing with one database, just script all objects out to a single file, and search that in an editor.

CODO ERGO SUM


I didn't even know that because I always use syscomments myself I was attempting to lead our young poster down the enlightened path of information_schema views rather than the system tables :)

Be One with the Optimizer
TG
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/22/2006 :  09:02:18  Show Profile  Reply with Quote
Here's some more info

http://weblogs.sqlteam.com/brettk/archive/2006/09/22/12454.aspx



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/22/2006 :  10:07:35  Show Profile  Reply with Quote
I can't remember where I got this script from (I assume it was from sqlteam but not sure) and it's an excellent search script like you're looking for:
USE [databasename]

DECLARE 
  @string varchar(1000),
  @ShowReferences char(1)

SET @string = 'mysearchterm' --> searchstring

SET @ShowReferences = 'N'
/****************************************************************************/
/*                                                                          */
/* TITLE:   sp_FindReferences                                               */
/*                                                                          */
/* DATE:    18 February, 2004                                               */
/*                                                                          */
/* AUTHOR:  WILLIAM MCEVOY                                                  */
/*                                                                          */
/****************************************************************************/
/*                                                                          */
/* DESCRIPTION:  SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */
/*                                                                          */
/****************************************************************************/
set nocount on

declare @errnum         int         ,
        @errors         char(1)     ,
        @rowcnt         int         ,
        @output         varchar(255)

select  @errnum         = 0         ,
        @errors         = 'N'       ,
        @rowcnt         = 0         ,
        @output         = ''        

/****************************************************************************/
/* INPUT DATA VALIDATION                                                    */
/****************************************************************************/


/****************************************************************************/
/* M A I N   P R O C E S S I N G                                            */
/****************************************************************************/

-- Create temp table to hold results
DECLARE @Results table
(
  Name        varchar(55),
  Type        varchar(12),
  DateCreated datetime,
  ProcLine    varchar(4000)
)


IF (@ShowReferences = 'N')
BEGIN
  insert into @Results
  select distinct
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         ''
    from sysobjects  SO
    join syscomments SC on SC.id = SO.id
   where SC.text like '%' + @string + '%'
  union
  select distinct
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         ''
    from sysobjects  SO
   where SO.name like '%' + @string + '%'
  union
  select distinct
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         ''
    from sysobjects  SO
    join syscolumns SC on SC.id = SO.ID
   where SC.name like '%' + @string + '%'
   order by 2,1
END
ELSE
BEGIN
  insert into @Results
  select 
         'Name'      = convert(varchar(55),SO.name),
         'Type'      = SO.type,
         crdate,
         'Proc Line' = text
    from sysobjects  SO
    join syscomments SC on SC.id = SO.id
   where SC.text like '%' + @string + '%'
  union
  select 
         'Name'      = convert(varchar(55),SO.name),
         'Type'      = SO.type,
         crdate,
         'Proc Line' = ''
    from sysobjects  SO
   where SO.name like '%' + @string + '%'
  union
  select 
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         'Proc Line' = ''
    from sysobjects  SO
    join syscolumns SC on SC.id = SO.ID
   where SC.name like '%' + @string + '%'
   order by 2,1
END


IF (@ShowReferences = 'N')
BEGIN
  select Name,
         'Type' = Case (Type)
                    when 'P'  then 'Procedure'
                    when 'TR' then 'Trigger'
                    when 'X'  then 'Xtended Proc'
                    when 'U'  then 'Table'
                    when 'C'  then 'Check Constraint'
                    when 'D'  then 'Default'
                    when 'F'  then 'Foreign Key'
                    when 'K'  then 'Primary Key'
                    when 'V'  then 'View'
                    else Type
                  end,
         DateCreated
    from @Results
    order by 2,1
END
ELSE
BEGIN
  select Name,
         'Type' = Case (Type)
                    when 'P'  then 'Procedure'
                    when 'TR' then 'Trigger'
                    when 'X'  then 'Xtended Proc'
                    when 'U'  then 'Table'
                    when 'C'  then 'Check Constraint'
                    when 'D'  then 'Default'
                    when 'F'  then 'Foreign Key'
                    when 'K'  then 'Primary Key'
                    when 'V'  then 'View'
                    else Type
                  end,
         DateCreated,
         ProcLine
    from @Results
    order by 2,1
END


--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

kasper
Starting Member

7 Posts

Posted - 11/27/2006 :  08:58:48  Show Profile  Reply with Quote
Thanks a lot for information!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 11/27/2006 :  09:06:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If you have some SP that does dynamic SQL, you will miss them.
I just thought you should be aware of that.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000