Tracking columns in stored procedure

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 2000 Forums

Transact-SQL (2000)

Tracking columns in stored procedure

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-20 : 03:07:21

what select query should i use to get the columns referenced in a stored procedure.For eg:- If emp_id is being used in 10 different stored procedure.what query should i use to get the stored procedures using the emp_id.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-20 : 03:24:46

Try this

Select specific_name from Information_Schema.routines
where routine_definition like '%columnName%'

Madhivanan

Failing to plan is Planning to fail

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-20 : 13:07:15

Thanks a lot...It works well....

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 06:49:49

Note that INFORMATION_SCHEMA.ROUTINES will only give you the first 4,000 characters of the Sproc - hopefully there is a way around that, but if not goodness knows why its been allowed to evolve braindead in that way

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-21 : 20:11:15

Probably the easiest thing to do would be to script out all the stored procedures in a database to a single file, open that with an editor, and search it with the find feature. You could also script procedures out to one file each and use Windows Explorer to search the text in the files.

Also, you can search the SYSCOMMENTS table with this query. One weakness of this method is that stored procedure code can be split across multiple rows, so if the string you are searching for happens to be split across two rows, you might not find it.


--Script to search for a string in stored procedure code

select
	crdate,
	a.[OBJECT_NAME]
from
	(
	select
		crdate = convert(varchar(23),a.crdate,121),
		[OBJECT_NAME] = convert(nvarchar(300),
				'['+c.name+'].'+
				'['+a.name+']')
	from
		SYSOBJECTS a
		join
		SYSCOMMENTS b
		on (a.id = b.id )
		join
		SYSUSERS c
		on (a.uid = c.uid)
	where
		-- String to search for in Stored Procedures
		b.text like '%date%'  escape '^'
		-- Object type P = Stored Procedures
		and a.type = 'p'
	) a
group by
	 crdate, a.[OBJECT_NAME]
order by
	 crdate, a.[OBJECT_NAME]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources