从数据库中删除
问题描述:
我想删除数据库中每个表的特定属性。例如:我想从数据库中的每个表中删除值为'2'的CustomerID
(列名)从数据库中删除
我试图删除有客户字段的记录,并且其值为2但我得到一个错误,说有关键字删除附近有不正确的语法
declare @SearchTerm nvarchar(4000)
declare @ColumnName sysname
set @SearchTerm = N'2' -- Term to be searched for
set @ColumnName = N'customerID' --**column**
set nocount on
declare @TabCols table (
id int not null primary key identity
, table_schema sysname not null
, table_name sysname not null
, column_name sysname not null
, data_type sysname not null
)
insert into @TabCols (table_schema, table_name, column_name, data_type)
select
t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
from INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA
and t.TABLE_NAME = c.TABLE_NAME
where 1 = 1
and t.TABLE_TYPE = 'base table'
and c.DATA_TYPE not in ('image', 'sql_variant')
and c.COLUMN_NAME like case when len(@ColumnName) > 0 then @ColumnName else '%' end
order by c.TABLE_NAME, c.ORDINAL_POSITION
declare
@table_schema sysname
, @table_name sysname
, @column_name sysname
, @data_type sysname
, @exists nvarchar(4000) -- Can be max for SQL2005+
, @sql nvarchar(4000) -- Can be max for SQL2005+
, @where nvarchar(4000) -- Can be max for SQL2005+
, @run nvarchar(4000) -- Can be max for SQL2005+
while exists (select null from @TabCols) begin
select top 1
@table_schema = table_schema
, @table_name = table_name
-- , @exists = 'select null from [' + table_schema + '].[' + table_name + '] where 1 = 0'
, @sql = 'delete''' + '[' + table_schema + '].[' + table_name + ']' + ''' as TABLE_NAME, from [' + table_schema + '].[' + table_name + '] where 1 = 0'
, @where = ''
from @TabCols
order by id
while exists (select null from @TabCols where table_schema = @table_schema and table_name = @table_name) begin
select top 1
@column_name = column_name
, @data_type = data_type
from @TabCols
where table_schema = @table_schema
and table_name = @table_name
order by id
-- Special case for money
if @data_type in ('money', 'smallmoney') begin
if isnumeric(@SearchTerm) = 1 begin
set @where = @where + ' or [' + @column_name + '] = cast(''' + @SearchTerm + ''' as ' + @data_type + ')' -- could also cast the column as varchar for wildcards
end
end
-- Special case for xml
else if @data_type = 'xml' begin
set @where = @where + ' or cast([' + @column_name + '] as nvarchar(max)) like ''' + @SearchTerm + ''''
end
-- Special case for date
else if @data_type in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') begin
set @where = @where + ' or convert(nvarchar(50), [' + @column_name + '], 121) like ''' + @SearchTerm + ''''
end
-- Search all other types
else begin
set @where = @where + ' or [' + @column_name + '] like ''' + @SearchTerm + ''''
end
delete from @TabCols where table_schema = @table_schema and table_name = @table_name and column_name = @column_name
end
set @run = 'if exists(' + @exists + @where + ') begin ' + @sql + @where + ' print ''' + @table_name + ''' end'
print @run
exec sp_executesql @run
end
set nocount off
答
这里是另一种基于ewahner发布的例子。最大的区别是这不使用游标,因为你真的不需要这个游标。
declare @columnName nvarchar(255)
declare @intValue int
set @columnName = 'CustomerId'
set @intValue = 1
declare @DeleteValue varchar(10)
set @DeleteValue = convert(varchar(10), @intValue)
declare @sql nvarchar(max) = ''
select @sql = @sql + 'delete ' + object_name(c.object_id) + ' where ' + @columnName + ' = ' + @DeleteValue + ';'
from sys.columns c
where c.name = @columnName
select @sql
/* Uncomment the line below in order to run */
--exec sp_executesql @sql
答
使用需要您自担风险。您将需要取消注释执行行。但首先运行它,不要取消注释,以确保它获得所需的表格。
declare @columnName nvarchar(255)
declare @intValue int
set @columnName = 'CustomerId'
set @intValue = 1
declare curs cursor for
select object_name(c.object_id) as tableName
from sys.columns c
where c.name = @columnName
declare @tableName nvarchar(255)
open curs
fetch next from curs into @tableName
while @@fetch_status = 0
begin
declare @sql nvarchar(1000)
set @sql = 'delete ' + @tableName + ' where ' + @columnName + '=' + convert(varchar(10), @intValue)
print @sql
/* Uncomment the line below in order to run */
--exec sp_executesql @sql
fetch next from curs into @tableName
end
close curs
deallocate curs
你的问题是什么? – dasblinkenlight
为什么它不会删除我从数据库中所有表中设置的值? – royalblue
您应该使用sys.tables和sys.columns,而不是传统的INFORMATION_SCHEMA视图。 http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx –