July 5, 2010 at 8:26 am
/*
I have been tasked to query all tables in a database which contain a specific column and delete all records from each such table where the value of the specified column is a specific string value.
I have obtained a list of the table names which contain a specific column as so:
*/
CREATE TABLE #TableNames (TableName nvarchar(32))
CREATE TABLE #RowCounts (TableName nvarchar(32), NumRows int)
INSERT INTO #TableNames(TableName)
SELECT OBJECT_NAME(object_id) AS TableID
FROM sys.columns
WHERE name = 'CUSTNMBR'
/*
Now the following script fails with this error:
Msg 1087, Level 15, State 2, Line 19
Must declare the table variable "@TBL".
*/
DECLARE @TBL nvarchar(32)
DECLARE @cst nvarchar(8)
SET @cst = 'A10285'
WHILE EXISTS(SELECT TOP 1 TableName FROM #TableNames ORDER BY TableName)
BEGIN
SET @TBL = (SELECT TOP 1 TableName FROM #TableNames ORDER BY TableName)
INSERT INTO #RowCounts(TableName,NumRows)
SELECT @TBL,COUNT(CUSTNMBR)
FROM @TBL
WHERE CUSTNMBR = @cst
DELETE FROM #TableNames WHERE TableName = @TBL
END
--Please help me understand how to do this correctly.
July 5, 2010 at 8:33 am
You can't reference a table by a variable. To get around this, you will have to use dynamic sql.
Something like:
declare @SqlCmd nvarchar(max)
set @SqlCmd = 'INSERT INTO #RowCounts(TableName,NumRows)
SELECT ''' + @TBL + ''',COUNT(CUSTNMBR)
FROM [' + @TBL + ']
WHERE CUSTNMBR = @cst'
execute sp_executesql @SqlCmd, N'@CST int', @cst
Note: I'm not sure if I got everything correct with the syntax to make this work - it's just to give you an idea of what you need to do.
Edit: @SqlCmd needs to be nvarchar(max), not varchar(max), to work with sp_executesql
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 5, 2010 at 10:33 am
In the past when I have needed to do this I used a script like this to generate the delete statements so that I would be able to review exactly what was going to be deleted prior to actually running it. However this did not take into acount referential integrity constraints:
DECLARE @column_name VARCHAR(255)
DECLARE @where_clause VARCHAR(255)
SET @column_name = 'lyn_id'
SET @where_clause = 1
SELECT 'DELETE FROM '+name+ ' WHERE '+@column_name+'='+@where_clause FROM sysobjects WHERE id IN(
SELECT id FROM syscolumns WHERE name LIKE @column_name)
The result would be:
DELETE FROM ACHM_LOOKUP_YES_NO WHERE lyn_id=1
DELETE FROM AHCM_WORK_ORDER_ATTACHMENT WHERE lyn_id=1
July 5, 2010 at 11:14 am
try this proc...it will generate some SELECT statements to help you find the data, which you can easily change to DELETE statements after review, or help you to decide to UPDATE instead.
be careful with this... especially without the second parameter....it cursors thru EVERY table with varchar/char type columns and does a table scan (because of the %searchterm%) for each column that might have the value to search for....so if you have a MillionBillion row table that has 40 varchar columns...that's 40 huge tablescans...
typical results: for @SearchTerm,@ColumnName
--finds any table with a column named 'CITYNAME' with a value LIKE % SearchTerm %
EXEC sp_UGLYSEARCH 'TEST','CITYNAME'
SCHEMANAME TBLNAME COLNAME SQL
dbo CITYDATA CITYNAME SELECT * FROM [dbo].[CITYDATA] WHERE [CITYNAME] LIKE '%TEST%'
the proc:
CREATE PROCEDURE sp_UGLYSEARCH
-- EXEC sp_UGLYSEARCH 'TEST','CITYNAME'
@SEARCHSTRING VARCHAR(50),
@KNOWNCOLNAME VARCHAR(50)= NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql VARCHAR(max),
@SCHEMANAME VARCHAR(100),
@TABLENAME VARCHAR(100),
@COLUMNNAME VARCHAR(100)
CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))
SELECT
SCHEMA_NAME(schema_id) AS SCHEMANAME,
sys.objects.name AS TBLNAME,
sys.columns.name AS COLNAME,
TYPE_NAME(sys.columns.user_type_id) AS DATATYPE
INTO #FKFINDER
FROM sys.objects
INNER JOIN sys.columns ON sys.objects.object_id=sys.columns.object_id
WHERE sys.objects.type='U'
AND TYPE_NAME(sys.columns.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND sys.columns.name = CASE
WHEN @KNOWNCOLNAME IS NULL
THEN sys.columns.name
ELSE @KNOWNCOLNAME
END
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #FKFINDER ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @sql = 'IF EXISTS(SELECT * FROM [' + @SCHEMANAME + '].[' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''%' + @SEARCHSTRING + '%'')
INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES(''' + @SCHEMANAME + ''',''' + @TABLENAME + ''',''' + @COLUMNNAME + ''',''
SELECT * FROM [' + @SCHEMANAME + '].[' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''''%' + @SEARCHSTRING + '%'''' '') ;'
--PRINT @sql
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
END --PROC
GO
Lowell
July 6, 2010 at 2:15 pm
Thanks to Wayne and Lowell.
My project is currently on hold, but I have been able by using a modified version of Lowell's script to find the tables containing records slated for deletion.
I like the look of Wayne's script but still have not resolved problems with the @Statement syntax.
Thanks again for your help.
July 6, 2010 at 4:05 pm
crossmj (7/6/2010)
Thanks to Wayne and Lowell.My project is currently on hold, but I have been able by using a modified version of Lowell's script to find the tables containing records slated for deletion.
I like the look of Wayne's script but still have not resolved problems with the @Statement syntax.
Thanks again for your help.
What problems are you having with the @Statement syntax?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 7, 2010 at 8:25 am
Wayne,
It took me awhile to understand this message:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
I finally understood the problem was not with how the SQL statement was formatted but with the datatype of the statement variable which was set to varchar instead of nvarchar.
I have now successfully adapted your solution as follows:
--create temporary tables
CREATE TABLE #TableNames (TableName nvarchar(32))
CREATE TABLE #RowCounts (TableName nvarchar(32), NumRows int)
--get all of the tables with a column named CUSTNMBR
INSERT INTO #TableNames(TableName)
SELECT OBJECT_NAME(object_id) AS TableID
FROM sys.columns
WHERE name = 'CUSTNMBR'
--query each table found for count of specific customer id
DECLARE @SqlCmd nvarchar(max)
DECLARE @TBL nvarchar(32)
DECLARE @cst nvarchar(8)
SET @cst = 'A10285'
WHILE EXISTS(SELECT TOP 1 TableName FROM #TableNames ORDER BY TableName)
BEGIN
SET @TBL = (SELECT TOP 1 TableName FROM #TableNames ORDER BY TableName)
SET @SqlCmd = 'INSERT INTO #RowCounts(TableName,NumRows) ' +
'SELECT ''' + @TBL + ''',COUNT(CUSTNMBR) ' +
'FROM [' + @TBL + '] WHERE CUSTNMBR = @CustID;'
EXEC sp_executesql @SqlCmd, N'@CustID nvarchar(8)', @CustID = @cst
DELETE FROM #TableNames WHERE TableName = @TBL
END
--verify processing of all tables
SELECT * FROM #TableNames
--view counts per table where customer id found
SELECT * FROM #RowCounts WHERE NumRows > 0
--drop temporary tables
DROP TABLE #TableNames
DROP TABLE #RowCounts
I have yet to work in any functionality for deleting records but this identification of tables to target helps alot.
July 7, 2010 at 10:46 am
crossmj (7/7/2010)
Wayne,It took me awhile to understand this message:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
I finally understood the problem was not with how the SQL statement was formatted but with the datatype of the statement variable which was set to varchar instead of nvarchar.
Whoops! :blush:
You're absolutely correct, that should be nvarchar instead of varchar. Glad you figured it out.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply