October 17, 2008 at 9:11 am
We're converting several tables to new table names and field names. We have reporting that use sprocs which call those current tables/fields.
My challenge and question is how to run some kind of script that would return a list identifying those sprocs that reference the tables that are being converted and will no longer be used effective a certain date.
It is critical that we include all the sprocs that will be affected by this cut-over, but we're trying to avoid checking one sproc at a time. This would be very counter-productive.
P.S. My co-worker ran some code which did list most of the sprocs affected, but it left out some too. What we learned is that his script is limiting the search to the first 4000 position, so if the table is referenced after 4000, it wouldn't pick it up.
thx,
John
October 17, 2008 at 9:18 am
Try -
DECLARE @ObjectName SYSNAME,
@ObjectType VARCHAR(5)
SET @ObjectName = 'etl_status'
SET @ObjectType = NULL
BEGIN
DECLARE @ObjectID AS BIGINT
SELECT TOP(1) @ObjectID = object_id
FROM sys.objects
WHERE name = @ObjectName
AND type = ISNULL(@ObjectType, type)
SET NOCOUNT ON ;
WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)
AS
(
SELECT DISTINCT
sd.object_id,
OBJECT_NAME(sd.object_id),
ReferencedObject = OBJECT_NAME(sd.referenced_major_id),
ReferencedObjectID = sd.referenced_major_id
FROM
sys.sql_dependencies sd
JOIN sys.objects so ON sd.referenced_major_id = so.object_id
WHERE
sd.referenced_major_id = @ObjectID
UNION ALL
SELECT
sd.object_id,
OBJECT_NAME(sd.object_id),
OBJECT_NAME(referenced_major_id),
object_id
FROM
sys.sql_dependencies sd
JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID
WHERE
sd.referenced_major_id <> sd.object_id
)
SELECT DISTINCT
DependentObjectName
FROM
DependentObjectCTE c
END
Tommy
Follow @sqlscribeOctober 17, 2008 at 9:55 am
I just tried to run the code but I'm getting error msg:
Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '('.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'WITH'.
October 17, 2008 at 11:18 am
I have a custom solution that will return not only the stored procedures, views, or triggers that have a specified search string in them, but also what line number the searched string is on. Run this in the database you want to search:
CREATE PROCEDURE #TSQL_Search (
@SearchString VARCHAR(40)
) AS
DECLARE
@SchemaName VARCHAR(128),
@ObjectName VARCHAR(128),
@ObjectType VARCHAR(128),
@Definition VARCHAR(max),
@CRLF CHAR(2),
@LineNum INT,
@LineDef VARCHAR(256),
@LineStartPos INT,
@LineEndPos INT,
@CharPos INT
BEGIN
SET NOCOUNT ON
SET @CRLF = CHAR(13) + CHAR(10)
DECLARE @ResultList TABLE (SchemaName VARCHAR(128), ObjectName VARCHAR(128), ObjectType VARCHAR(128), LineNum int, SQL VARCHAR(256))
DECLARE cur_Grep CURSOR FAST_FORWARD FOR
SELECT s.name AS schema_name, o.name AS object_name, o.type_desc, m.definition
FROM sys.objects o
INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE m.definition like '%' + @SearchString + '%'
ORDER BY s.name, o.type_desc, o.name
OPEN cur_Grep
FETCH NEXT FROM cur_Grep INTO @SchemaName, @ObjectName, @ObjectType, @Definition
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LineNum = 1
SET @LineStartPos = 1
SET @LineEndPos = CHARINDEX(@CRLF, @Definition)
WHILE @LineStartPos > 0
BEGIN
IF @LineEndPos > 0
SET @LineDef = SUBSTRING(@Definition, @LineStartPos, @LineEndPos-@LineStartPos+1)
ELSE
SET @LineDef = SUBSTRING(@Definition, @LineStartPos, LEN(@Definition)-@LineStartPos+1)
SET @CharPos = CHARINDEX(@SearchString, @LineDef)
IF @CharPos > 0
INSERT INTO @ResultList VALUES (@SchemaName, @ObjectName, @ObjectType, @LineNum, @LineDef)
SET @LineStartPos = @LineEndPos
SET @LineEndPos = CHARINDEX(@CRLF, @Definition, @LineStartPos+2)
SET @LineNum = @LineNum + 1
END
FETCH NEXT FROM cur_Grep INTO @SchemaName, @ObjectName, @ObjectType, @Definition
END
CLOSE cur_Grep
DEALLOCATE cur_Grep
SELECT * FROM @ResultList
END
call it like this:
EXEC #TSQL_Search 'somesearchstring'
October 17, 2008 at 11:25 am
latingntlman (10/17/2008)
I just tried to run the code but I'm getting error msg:Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '('.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'WITH'.
You are running this on SQL 2005, right? 🙂
Tommy
Follow @sqlscribeOctober 17, 2008 at 11:50 am
Although I'm using MSSQL Server Mgmt Studio, I'm querying DB's in 2000 and 2005. Why?
October 17, 2008 at 12:08 pm
latingntlman (10/17/2008)
Although I'm using MSSQL Server Mgmt Studio, I'm querying DB's in 2000 and 2005. Why?
The script I sent you was for SQL 2005 - CTE (Common Table Expression's) and SQL 2005's dmv's don't exist in SQL 2000 🙂
You might want to post this in the SQL 2000 General Discussion forum 🙂
Tommy
Follow @sqlscribeOctober 27, 2008 at 9:38 am
Tommy,
Thanks for the headsup. However, in sql 2005, I executed the sproc as exec #TSQL_Search 'ReportProcessing' (ReportProcessing being the Db) where there are a bunch of sprocs but the results pane shows zero records. Am I typing the wrong syntax or do I need to include the server??
I'm confused.. :crazy:
John
October 27, 2008 at 9:44 am
latingntlman (10/27/2008)
exec #TSQL_Search 'ReportProcessing' (ReportProcessing being the Db) where there are a bunch of sprocs but the results pane shows zero records.
John,
sorry for the confusion. If you're using my #TSQL_Search script, you'd do something like:
USE ReportProcessing
(the script to create the temporary procedure)
EXEC #TSQL_Search 'sometablename'
this would tell you all the views, triggers, stored procedures, and UDFs that are referencing sometablename
October 27, 2008 at 9:45 am
latingntlman (10/27/2008)
Tommy,Thanks for the headsup. However, in sql 2005, I executed the sproc as exec #TSQL_Search 'ReportProcessing' (ReportProcessing being the Db) where there are a bunch of sprocs but the results pane shows zero records. Am I typing the wrong syntax or do I need to include the server??
I'm confused.. :crazy:
John
Hi John - NP. The script I provided is just that, not to be confused with a stored procedure. To execute the script, launch SSMS (SQL Server Management Studio), click on the new query tab, and select the database from the drop-down menu (i.e. ReportServer). Copy and paste the script into the query window and press F5 to execute it.
Thanks 🙂
Tommy
Follow @sqlscribeOctober 28, 2008 at 7:17 am
latingntlman (10/17/2008)
We're converting several tables to new table names and field names. We have reporting that use sprocs which call those current tables/fields.My challenge and question is how to run some kind of script that would return a list identifying those sprocs that reference the tables that are being converted and will no longer be used effective a certain date.
It is critical that we include all the sprocs that will be affected by this cut-over, but we're trying to avoid checking one sproc at a time. This would be very counter-productive.
P.S. My co-worker ran some code which did list most of the sprocs affected, but it left out some too. What we learned is that his script is limiting the search to the first 4000 position, so if the table is referenced after 4000, it wouldn't pick it up.
thx,
John
We use this at work:
declare @search varchar(128)
declare @prefix varchar(20)
set @search=''
set @prefix=''
select xtype, name, substring(text,charindex(@search,text)-20,20)+substring(text,charindex(@search,text),50)
from sysobjects, syscomments
where syscomments.id=sysobjects.id
and text like '%' + @search + '%'
and name like @prefix + '%'
order by name
October 28, 2008 at 8:14 am
Thanks Tommy, it works!! Now, what if I need to see results for more than one table by executing the temp sproc only once? i.e.
Exec #TSQL_Search 'Table1,Table2'
I tried it this way but gave me an error msg.
thx,
John
October 28, 2008 at 8:27 am
latingntlman (10/28/2008)
Thanks Tommy, it works!! Now, what if I need to see results for more than one table by executing the temp sproc only once? i.e.Exec #TSQL_Search 'Table1,Table2'
I tried it this way but gave me an error msg.
thx,
John
Hi John, assuming you are using the SP provided by Chris Harshman (exec #TSQL_SEARCH 'Table'); then just use dynamic sql within a while loop to iterate through sys.tables. i.e.
declare @worktbl table
(table_name sysname)
insert into @worktbl
select s.name + '.' + t.name as table_name
from sys.tables t with(nolock)
join sys.schemas s with(nolock)
on s.schema_id = t.schema_id
declare @tbl_name sysname, @tbl_count int, @sql nvarchar(max)
select @tbl_count = count(*) from @worktbl
while @tbl_count > 0
begin
select top 1 @tbl_name = table_name from @worktbl
--exec sp_help @tbl_name
exec #TSQL_SEARCH @tbl_name
delete from @worktbl where table_name = @tbl_name
select @tbl_count = count(*) from @worktbl
end
Tommy
Follow @sqlscribeViewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply