How to find the usage of constant in sql server database
Is there a way to search through the set of (Stored Procedures, Functions, Views) for the usage of a constant?
I have a problem wh开发者_运维问答ere I've got a sql server database. It has quite a few stored procedures and functions declared. I'm looking for the usage of "115", which happens to be a pay code. I didn't write all of the code originally, so I'm looking for anywhere that a constant is declared, or the string "115" is used literally.
SELECT * FROM sys.objects WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%115%'
This includes constraints etc too that are omitted in sys.sql_modules and sys.procedures
You can search sys.sql_modules for definition LIKE N'%115%'
:
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name
, o.type, o.type_desc
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition LIKE N'%115%'
ORDER BY o.type;
You may get a bunch of false positives, but at least you have a starting point.
SQL Server 2000:
USE AdventureWorks
GO
--Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Employee%'
GO
--Option 2
SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%Employee%'
GO
SQL Server 2005:
USE AdventureWorks
GO
--Searching for Empoloyee table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
GO
--Searching for Empoloyee table and RateChangeDate column together
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%RateChangeDate%'
GO
Both examples taken from:
http://blog.sqlauthority.com/2007/11/10/sql-server-2005-2000-search-string-in-stored-procedure/
Similarly:
http://www.knowdotnet.com/articles/storedprocfinds.html
with this example:
CREATE PROCEDURE Find_Text_In_SP
@StringToSearch varchar(100)
AS
SET @StringToSearch = '%' +@StringToSearch + '%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
GO
精彩评论