November 30, 2010 at 4:06 am
Hi.
Apologies for this, but I'm fairly confident I'm doing something stupid but I have a cursor that seems to be returning the first result on every pass.
The script is to find all if a list of table names occur in any of the stored procedures in a database. The list of tables are new and altered tables in an upgrade to a package solution. I have a query that works:
SELECT TOP 1 ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '{NAME OF TABLE}' AND ROUTINE_TYPE='PROCEDURE'
If I manually insert the table name I get a result
So running
Declare @Table varchar(10) SET @Table = 'ARTIKEL'
(SELECT TOP 1 ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@Table+'%' AND ROUTINE_TYPE='PROCEDURE')
Returns a procedure name.
However I have almost 200 to check many of which I expect to return null.
I've have a table with the list of all new and changed tables. [TableChanges320] This has a flag identifying if the table is new to the the solution. I then load the 'not new' tables into a temp table then use a cursor to update #Tmp using the subquery, but every row returns null!
Is there anything about INFORMATION_SCHEMA that prevents me from using it as a subquery, or am I just looking at it and seeing what I expect to seem rather than what is typed?
I'm quite prepared to be the nitwit of the day! 😉
Full script below.
Create Table #Tmp (
FutTable varchar(10),
StoredProc varchar(255)
)
INSERT INTO #Tmp
SELECT [Name], ''
FROM [XREF].[dbo].[TableChanges320]
where New IS NULL
Declare @Table varchar(10)
Declare @SProc varchar(255)
Declare cur1 CURSOR FOR
SELECT FutTable
FROM #Tmp FOR UPDATE
OPEN cur1
FETCH NEXT FROM cur1 INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SProc =
(SELECT TOP 1 ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@Table+'%' AND ROUTINE_TYPE='PROCEDURE')
UPDATE #Tmp set StoredProc = @SProc
FETCH NEXT FROM cur1 INTO @Table
END
CLOSE cur1
DEALLOCATE cur1
SELECT * FROM #Tmp
November 30, 2010 at 4:44 am
Robin_D (11/30/2010)
UPDATE #Tmp set StoredProc = @SProc
Shouldn't there be a where clause on that update?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2010 at 4:56 am
I just knew it was blindingly obvious. :crazy:
Thanks for that Gail.
"I should have gone to Specsavers".:-)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply