Loop through all tables and delete records
I'm new to MsSql and I'm not sure if this can be done, but I figured I'd ask before I want to get in my way with the current process.
I need to create a script that iterates over all tables in a database and deletes rows where CorporateId = "xxx". There are some tables that don't have this column, but out of my ~ 50 tables, only 1 or 2 don't.
I can individually delete records in a table with this:
USE MyDatabase
DECLARE @CorporationId UniqueIdentifier
DECLARE @TokenId UniqueIdentifier
DECLARE @CompanyCode nChar(3)
SET @CorporationId = '52D3AEFE-8EBD-4669-8096-4596FE83BB36'
print 'Starting Web.GasOrder'
DELETE FROM Web.GasOrder
WHERE CorporationId = @CorporationId
print 'Starting Web.GasOrderNumber'
DELETE FROM Web.GasOrderNumber
WHERE CorporationId = @CorporationId
etc..
But it gets tedious creating it for every table.
Of course, some of the tables have relationships to them.
Is there an easy way to do this or do I have to do it manually for each table?
UPDATE
Most of the options I've come across run into relationship problems and give me the error.
source to share
Here's another one ... can be easily changed to a stored procedure ...
Declare @corpID Nvarchar(256)
Set @corpID = 'xxx'
If Object_ID('tempdb..#tables') Is Not Null Drop Table #tables
Create Table #tables (tID Int, SchemaName Nvarchar(256), TableName Nvarchar(256))
Insert #tables
Select Row_Number() Over (Order By s.name, so.name), s.name, so.name
From sysobjects so
Join sys.schemas s
On so.uid = s.schema_id
Join syscolumns sc
On so.id = sc.id
Where so.xtype = 'u'
And sc.name = 'CorporationId'
Declare @SQL Nvarchar(Max),
@schema Nvarchar(256),
@table Nvarchar(256),
@iter Int = 1
While Exists (Select 1
From #tables)
Begin
Select @schema = SchemaName,
@table = TableName
From #tables
Where tID = @iter
If Exists (Select 1
From sysobjects o
Join sys.schemas s1
On o.uid = s1.schema_id
Join sysforeignkeys fk
On o.id = fk.rkeyid
Join sysobjects o2
On fk.fkeyid = o2.id
Join sys.schemas s2
On o2.uid = s2.schema_id
Join #tables t
On o2.name = t.TableName Collate Database_Default
And s2.name = t.SchemaName Collate Database_Default
Where o.name = @table
And s1.name = @schema)
Begin
Update t
Set tID = (Select Max(tID) From #tables) + 1
From #tables t
Where tableName = @table
And schemaName = @schema
Set @iter = @iter + 1
End
Else
Begin
Set @Sql = 'Delete t
From [' + @schema + '].[' + @table + '] t
Where CorporationId = ''' + @corpID + ''''
Exec sp_executeSQL @SQL;
Delete t
From #tables t
Where tableName = @table
And schemaName = @schema
Set @iter = @iter + 1
End
End
source to share
sp_MSForEachTable
is an undocumented stored procedure that will run a command for every table in the database:
USE MyDatabase
DECLARE @CorporationId VARCHAR(50)
SET @CorporationId = '52D3AEFE-8EBD-4669-8096-4596FE83BB36'
DECLARE @Sql VARCHAR(MAX)
SET @Sql = '
IF COL_LENGTH(''?'',''CorporationId'') IS NOT NULL
BEGIN
DELETE FROM Web.?
WHERE CorporationId = ''' + @CorporationId + '''
END
'
EXEC sp_MSForEachTable @Sql
source to share
You can run a query like this:
SELECT 'DELETE FROM [' + s.name + '].[' + t.name + '] WHERE CorporationId = ''52D3AEFE-8EBD-4669-8096-4596FE83BB36'''
FROM sys.columns c
inner join sys.tables t
ON t.object_id= c.object_id
inner join sys.schemas s
ON s.schema_id = t.schema_id
where c.name = 'CorporationId'
and then either copy and paste the results into a new query window, or execute a new query, or iterate through the results with a cursor and execute each result with a statement exec
.
source to share
Here is a Sql Fiddle that the query below proves
You can get tables from this query:
SELECT Name, IsChecked = 0
INTO #Tables
FROM sys.Tables
WHERE EXISTS
(
SELECT * FROM sys.columns
WHERE object_id = OBJECT_ID(sys.Tables.Name) AND sys.columns.Name = 'blah'
)
Then you can create a dynamic query and execute it on the tables found
WHILE(SELECT COUNT(*) FROM #Tables WHERE IsChecked = 0) > 0
BEGIN
SELECT TOP 1 @TableName = Name FROM #Tables WHERE IsChecked = 0
SET @DeleteQuery = 'DELETE FROM ' + @TableName + ' WHERE CorporationID = ''' + @CorporationId + ''''
EXECUTE sp_executeSQL @DeleteQuery;
UPDATE #Tables SET IsChecked = 1 WHERE Name = @TableName
END
source to share