Finding a column in database objects

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Finding a column in database objects

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-09-24 : 06:03:56

Hi i have an unsual request where i need to find a column name in a datbase where it is being used. I have already gotten where it's been used in the tables and views but is there a way to find out where its been used in a stored procedure and in schedule job ?

Here is the code i used for the table and view

SELECT * FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'pid_code' )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 06:08:59

[code]SELECT TABLE_SCHEMA,
TABLE_NAME,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'pid_code'[/code]

E 12°55'05.63"
N 56°04'39.26"

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2008-09-24 : 06:12:52

SQL Code:
"sys.sql_modules contains a row for each object that is a SQL language-defined module.
Objects of type P, RF, V, TR, FN, IF, TF, R, and D have an associated SQL module."
select object_name(object_id), * from sys.sql_modules
where definition like '%pid_code%'

Job Steps:
select * from msdb.dbo.sysjobsteps
where subsystem = 'TSQL'
and command like '%sproc%'

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-09-24 : 06:22:19

This gives me tables and views but no stored procedures. is its possible to find where a column is being used in a stored procedures also

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-24 : 06:35:36

Try

select object_name(object_id) as proc_name from sys.sql_modules
where definition like '% colname %'

Madhivanan

Failing to plan is Planning to fail

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-09-24 : 06:49:07

Thanks i got the them to work and here is another one its define by the ROUTINE_TYPE='PROCEDURE'

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%pid_code%'
AND ROUTINE_TYPE='PROCEDURE'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources