Searching metadata - SQL Server Forums

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

Transact-SQL (2005)

Searching metadata

Author

Topic

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2010-02-10 : 11:09:22

I have a list of fields from the mainframe that will be changed and I need to scan all our stored procedures to identify any references to these fields. With the following table, what would be a good way to search thousands of SPs in hundreds of databases?

CREATE TABLE #field_Names
(
Field varchar (20) NOT null
)

INSERT INTO #field_Names
SELECT 'CSTOLQ'
UNION ALL SELECT 'DISTLQ'
UNION ALL SELECT 'ISTSLQ'
UNION ALL SELECT 'OVUCLQ'
UNION ALL SELECT 'QSTSLQ'
UNION ALL SELECT 'TKEN'
UNION ALL SELECT 'CSTOLQ'
UNION ALL SELECT 'DISTLQ'
UNION ALL SELECT 'ISTSLQ'
UNION ALL SELECT 'OVUCLQ'
UNION ALL SELECT 'QSTSLQ'

go

CREATE PROCEDURE Test_target AS
SELECT 'TKEN'
SELECT 'OVUCLQ'

go

The output should be something like

TKEN dbo.Test_Target
OVUCLQ dbo.Test_Target

I should also mention that there are about 25,000 more records in "#field_Names".

An infinite universe is the ultimate cartesian product.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-10 : 11:25:19

The stored procedure scripts are stored in the sys.comments system table so you should be able to search for the specific column names. There is an undocumented stored procedure named sp_MSForEachDB that lets you run your search script on every database on your servfer.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2010-02-10 : 11:53:31

Actually I was planning on using information_schema.routines.

The main problem is creating an efficient search script, not running against each DB. For each SP I need to look for over 50,000 strings.

An infinite universe is the ultimate cartesian product.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 12:15:02

you could use information_schema.routines as long as procedure definition length is < 4000 as fields maxlength is nvarchar(4000). so I tend to use sys.sql_modules catalog view for the same as there the definition field is of type nvarchar(max).
However seeing your reqmnt above wont be an accurate or optimal solution as you can do only string compare within body based on column names which can bring unwanted results also ( like if you're looking for column like QSTSLQ particulary and if one of sp has reference to similar column like QSTSLQ_Def it can also get included as you wont be able to do absolute searches within procedure but its rather a string search.

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources