删除数据库中所有表中指定的字段

来源：互联网发布：星际抢票软件编辑：程序博客网时间：2024/06/09 17:03

– 更精确的查询

SELECT obj.name FROM SysObjects objLEFT JOIN syscolumns col ON col.id = obj.idWHERE obj.XType='U' AND col.name = 'CreateId'ORDER BY obj.Name

--定义变量DECLARE @strSql NVARCHAR(4000);DECLARE @strWhere NVARCHAR(1000);DECLARE @TableName NVARCHAR(100);DECLARE @strDelete  NVARCHAR(100);SET @strWhere = ' Where createid = ''170010000'' 'SET @strDelete = 'DELETE FROM '--定义游标DECLARE contact_cursor CURSOR FOR--检索当前数据库中所有的用户表SELECT Name AS TableName FROM SysObjects Where XType='U' ORDER BY Name--打开游标OPEN contact_cursorFETCH NEXT FROM contact_cursorINTO @TableNameWHILE @@FETCH_STATUS = 0BEGIN    --拼接SQL     SET @strSql = @strDelete + @TableName + @strWhere;    PRINT @strSql    --EXECUTE sp_executesql  @strSql    FETCH NEXT FROM contact_cursor       INTO @TableNameEND--关闭释放游标CLOSE contact_cursorDEALLOCATE contact_cursor

0 0