(SqlServer)批量清理指定数据库中所有数据（sql server批量删除数据库）

25-03-03 11

如果您对(SqlServer)批量清理指定数据库中所有数据感兴趣，那么本文将是一篇不错的选择，我们将为您详在本文中，您将会了解到关于(SqlServer)批量清理指定数据库中所有数据的详细内容，我们还

如果您对(SqlServer)批量清理指定数据库中所有数据感兴趣，那么本文将是一篇不错的选择，我们将为您详在本文中，您将会了解到关于(SqlServer)批量清理指定数据库中所有数据的详细内容，我们还将为您解答sql server批量删除数据库的相关问题，并且为您提供关于mssql sqlserver 使用 sql 脚本清空所有数据库表数据的方法分享、SQL Server SQL 统计一个数据库中所有表记录的数量、SQL Server 得到数据库中所有表的名称及数据条数、SQL Server 查询数据库中所有表数据条数的有价值信息。

本文目录一览：

(SqlServer)批量清理指定数据库中所有数据（sql server批量删除数据库）
mssql sqlserver 使用 sql 脚本清空所有数据库表数据的方法分享
SQL Server SQL 统计一个数据库中所有表记录的数量
SQL Server 得到数据库中所有表的名称及数据条数
SQL Server 查询数据库中所有表数据条数

(SqlServer)批量清理指定数据库中所有数据（sql server批量删除数据库）

[推荐] (sqlServer)批量清理指定数据库中所有数据

——通过知识共享树立个人品牌。

在实际应用中，当我们准备把一个项目移交至客户手中使用时，我们需要把库中所有表先前的测试数据清空，以给客户一个干净的数据库，如果涉及的表很多，要一一的清空，不仅花费时间，还容易出错以及漏删，在这儿我提供了一个方法，可快捷有效的清空指定数据库所有表的数据。仅供参考，欢迎交流不同意见。

-- Remove all data from a database

SET NOCOUNT ON
-- Tables to ignore
DECLARE @IgnoreTables
         TABLE (TableName varchar( 512))
INSERT INTO @IgnoreTables (TableName) VALUES ( ' sysdiagrams ')
DECLARE @AllRelationships
         TABLE (ForeignKey varchar( 512)
              ,TableName varchar( 512)
              ,ColumnName varchar( 512)
              ,ReferenceTableName varchar( 512)
              ,ReferenceColumnName varchar( 512)
              ,DeleteRule varchar( 512))
INSERT INTO @AllRelationships
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName,
delete_referential_action_desc as DeleteRule
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f. OBJECT_ID = fc.constraint_object_id

DECLARE @TableOwner varchar( 512)
DECLARE @TableName varchar( 512)
DECLARE @ForeignKey varchar( 512)
DECLARE @ColumnName varchar( 512)
DECLARE @ReferenceTableName varchar( 512)
DECLARE @ReferenceColumnName varchar( 512)
DECLARE @DeleteRule varchar( 512)

PRINT( ' Loop through all tables and switch all constraints to have a delete rule of CASCADE ')
DECLARE DataBaseTables0
CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
FROM sys.tables AS t;

OPEN DataBaseTables0;

FETCH NEXT FROM DataBaseTables0
INTO @TableOwner,@TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
     IF ( NOT EXISTS( SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName))
     BEGIN
         PRINT ' [ ' + @TableOwner + ' ].[ ' + @TableName + ' ] ';

         DECLARE DataBaseTableRelationships CURSOR FOR
         SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName
         FROM @AllRelationships
         WHERE TableName = @TableName

         OPEN DataBaseTableRelationships;
         FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;

         IF @@FETCH_STATUS <> 0
             PRINT ' =====> No Relationships ' ;

         WHILE @@FETCH_STATUS = 0
         BEGIN
             PRINT ' =====> switching delete rule on ' + @ForeignKey + ' to CASCADE ';
             BEGIN TRANSACTION
             BEGIN TRY
                 EXEC( '

                ALTER TABLE [ ' + @TableOwner + ' ].[ ' + @TableName + ' ]
                 DROP CONSTRAINT ' + @ForeignKey + ' ;

                ALTER TABLE [ ' + @TableOwner + ' ].[ ' + @TableName + ' ] ADD CONSTRAINT
                 ' + @ForeignKey + ' FOREIGN KEY
                (
                 ' + @ColumnName + '
                ) REFERENCES ' + @ReferenceTableName + '
                (
                 ' + @ReferenceColumnName + '
                ) ON DELETE CASCADE;
                 ');
                 COMMIT TRANSACTION
             END TRY
             BEGIN CATCH
                 PRINT ' =====> can '' t switch ' + @ForeignKey + ' to CASCADE, - ' +
                 CAST(ERROR_NUMBER() AS VARCHAR) + ' - ' + ERROR_MESSAGE();
                 ROLLBACK TRANSACTION
             END CATCH;

             FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ReferenceColumnName;
         END;

         CLOSE DataBaseTableRelationships;
         DEALLOCATE DataBaseTableRelationships;

         END
         PRINT '';
         PRINT '';

         FETCH NEXT FROM DataBaseTables0
         INTO @TableOwner,@TableName;
     END
CLOSE DataBaseTables0;
DEALLOCATE DataBaseTables0;

PRINT( ' Loop though each table and DELETE All data from the table ')

DECLARE DataBaseTables1 CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
FROM sys.tables AS t;

OPEN DataBaseTables1;

FETCH NEXT FROM DataBaseTables1
INTO @TableOwner,@TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
     IF ( NOT EXISTS( SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName))
     BEGIN
         PRINT ' [ ' + @TableOwner + ' ].[ ' + @TableName + ' ] ';
         PRINT ' =====> deleting data from [ ' + @TableOwner + ' ].[ ' + @TableName + ' ] ';
         BEGIN TRY
             EXEC( '
                 DELETE FROM [ ' + @TableOwner + ' ].[ ' + @TableName + ' ]
                 DBCC CHECKIDENT ([ ' + @TableName + ' ], RESEED, 0)
                  ');
         END TRY
         BEGIN CATCH
             PRINT ' =====> can '' t FROM [ ' + @TableOwner + ' ].[ ' + @TableName + ' ], - ' +
                   CAST(ERROR_NUMBER() AS VARCHAR) + ' - ' + ERROR_MESSAGE();
         END CATCH;
     END

     PRINT '';
     PRINT '';

     FETCH NEXT FROM DataBaseTables1
     INTO @TableOwner,@TableName;
END
CLOSE DataBaseTables1;
DEALLOCATE DataBaseTables1;

PRINT( ' Loop through all tables and switch all constraints to have a delete rule they had at the beggining of the task ')

DECLARE DataBaseTables2 CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
FROM sys.tables AS t;
OPEN DataBaseTables2;

FETCH NEXT FROM DataBaseTables2
INTO @TableOwner,@TableName;

WHILE @@FETCH_STATUS = 0
BEGIN

     IF ( NOT EXISTS( SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName))
     BEGIN
     PRINT ' [ ' + @TableOwner + ' ].[ ' + @TableName + ' ] ';

     DECLARE DataBaseTableRelationships CURSOR FOR
     SELECT ForeignKey, ReferenceColumnName, DeleteRule
     FROM @AllRelationships
     WHERE TableName = @TableName

     OPEN DataBaseTableRelationships;
     FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ReferenceColumnName, @DeleteRule;

     IF @@FETCH_STATUS <> 0
     PRINT ' =====> No Relationships ' ;

     WHILE @@FETCH_STATUS = 0
     BEGIN
         DECLARE @switchBackTo varchar( 50) =
         CASE
             WHEN @DeleteRule = ' NO_ACTION ' THEN ' NO ACTION '
             WHEN @DeleteRule = ' CASCADE ' THEN ' CASCADE '
             WHEN @DeleteRule = ' SET_NULL ' THEN ' SET NULL '
             WHEN @DeleteRule = ' SET_DEFAULT ' THEN ' SET DEFAULT '
         END

         PRINT ' =====> switching delete rule on ' + @ForeignKey + ' to ' + @switchBackTo;

         BEGIN TRANSACTION
         BEGIN TRY
             EXEC( '

            ALTER TABLE [ ' + @TableOwner + ' ].[ ' + @TableName + ' ]
            DROP CONSTRAINT ' + @ForeignKey + ' ;

            ALTER TABLE [ ' + @TableOwner + ' ].[ ' + @TableName + ' ] ADD CONSTRAINT
             ' + @ForeignKey + ' FOREIGN KEY
            (
             ' + @ColumnName + '
            ) REFERENCES ' + @ReferenceTableName + '
            (
             ' + @ReferenceColumnName + '
            ) ON DELETE ' + @switchBackTo + '
             ');

             COMMIT TRANSACTION
         END TRY
         BEGIN CATCH
             PRINT ' =====> can '' t change ' + @ForeignKey + ' back to ' + @switchBackTo + ' , - ' +
             CAST(ERROR_NUMBER() AS VARCHAR) + ' - ' + ERROR_MESSAGE();
             ROLLBACK TRANSACTION
         END CATCH;

         FETCH NEXT FROM DataBaseTableRelationships
         INTO @ForeignKey, @DeleteRule;
     END;

     CLOSE DataBaseTableRelationships;
     DEALLOCATE DataBaseTableRelationships;

     END
     PRINT '';
     PRINT '';

     FETCH NEXT FROM DataBaseTables2
     INTO @TableOwner,@TableName;
END
CLOSE DataBaseTables2;

DEALLOCATE DataBaseTables2;

原创作品，转贴请注明作者和出处，留此信息。

------------------------------------------------

cnBlobs:http://www.cnblogs.com/huyong/
CSDN：http://blog.csdn.net/chinahuyong

作者：EricHu（DB、C\S、B\S、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

Q Q：80368704 E-Mail: 80368704@qq.com
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看 [置顶]索引贴——（不断更新中）

mssql sqlserver 使用 sql 脚本清空所有数据库表数据的方法分享

原文: mssql sqlserver 使用 sql 脚本清空所有数据库表数据的方法分享

摘要：下文讲述清空数据库中所有表信息的方法分享，如下所示：实验环境:sql server 2008

实现思路: 1. 禁用所有约束，外键 2. 禁用所有触发器 3. 删除表数据 4. 开启触发器 5. 开启约束

CREATE PROCEDURE proc_deleteDateSql
 /*
  生成清除数据库表数据的脚本方法分享
 */
AS
EXEC sp_MSForEachTable ''ALTER TABLE ? NOCHECK CONSTRAINT ALL''
EXEC sp_MSForEachTable ''ALTER TABLE ? DISABLE TRIGGER ALL''
EXEC sp_MSForEachTable ''delete from ?''
EXEC sp_MSForEachTable ''ALTER TABLE ? CHECK CONSTRAINT ALL''
EXEC sp_MSForEachTable ''ALTER TABLE ? ENABLE TRIGGER ALL''
EXEC sp_MSFOREACHTABLE ''SELECT * FROM ?''
GO


--生成脚本信息
exec proc_deleteDateSql 

go

drop proc  proc_deleteDateSql 
go

此方法请谨慎使用，因为执行后，会导致所有数据丢失，并且不可恢复，如出现外键约束，最好先删除外键信息，

如: 批量删除 MSSQL 中主外键约束

转自:http://www.maomao365.com/?p=7544

SQL Server SQL 统计一个数据库中所有表记录的数量

感兴趣的小伙伴，下面一起跟随小编 jb51.cc的小编两巴掌来看看吧！

代码如下:

 
CREATE TABLE #tmptb(tbname sysname,tbrows int,tbREserved varchar(10),tbData varchar(10),tbIndexSize varchar(10),tbUnUsed varchar(10)) 
INSERT INTO #tmptb exec sp_MSForEachTable 'EXEC sp_spaceused ''?''' 
SELECT * from #tmptb --列出所有表的情况 
SELECT tbrows,tbname FROM #tmptb WHERE tbrows=0 --列出记录数据为0的表 
ORDER BY tbname 
DROP TABLE #tmptb

其中
tbname 表名
tbrows 记录数
tbREserved 保留空间
tbData 使用空间
tbIndexSize 索引使用空间
tbUnUsed 未用空间

SQL Server 得到数据库中所有表的名称及数据条数

b.name as tablename ,c.row_count as datacount from sys.indexes a ,sys.objects b ,sys.dm_db_partition_stats c b.b.c.a.index_id = c.index_id b.is_ms_shipped b.name as tablename ,a.rowcnt as datacount from sysindexes a ,sysobjects b where a.i

b.name as tablename , c.row_count as datacount from sys.indexes a , sys.objects b , sys.dm_db_partition_stats c b.b.c.a.index_id = c.index_id b.is_ms_shipped b.name as tablename , a.rowcnt as datacount from sysindexes a , sysobjects b where a.id = b.id (b.id, ) = 0 ，香港服务器租用，美国服务器，香港虚拟主机

SQL Server 查询数据库中所有表数据条数

有的时间我想把数据库中表的记录统计一下，如果我们一个一个表的操作可以直接select count(*) from tablename就可以然后一个个相加，但是如果有上百个表有没有更简单的方法呢,下面我总结了一些方法有需要的朋友可参考。

如果是要得到中所有表的条数呢？我们来看几种最常见的方式：

--方法一

代码如下

复制代码

b.name as tablename ,

c.row_count as datacount

from sys.indexes a ,

sys.objects b ,

sys.dm_db_partition_stats c

where a.[object_id] = b.[object_id]

AND b.[object_id] = c.[object_id]

AND a.index_id = c.index_id

AND a.index_id

AND b.is_ms_shipped = 0

--方法二

代码如下

复制代码

select b.name as tablename ,

a.rowcnt as datacount

from sysindexes a ,

sysobjects b

where a.id = b.id

and a.indid

and objectproperty(b.id, ''IsMSShipped'') = 0

--方法三

代码如下

复制代码

if exists ( select *
            from    dbo.sysobjects
            where   id = object_id(N''[dbo].[TableSpace]'')
                    and objectproperty(id, N''IsUserTable'') = 1 )
    drop table [dbo].[TableSpace]
go
create table TableSpace
    (
      TableName varchar(20) ,
      RowsCount char(11) ,
      Reserved varchar(18) ,
      Data varchar(18) ,
      Index_size varchar(18) ,
      Unused varchar(18)
    )
go
declare @sql varchar(500)
declare @TableName varchar(20)
declare mCursor cursor
for
select name from sysobjects where xtype=''U''
open mCursor
fetch NEXT from mCursor into @TableName
while @@fetch_status = 0
    begin
        set @sql = ''insert into TableSpace ''
        set @sql = @sql + '' exec sp_spaceused '''''' + @TableName + '''''' ''
        exec (@sql)
        fetch NEXT from mCursor into @TableName
    end
close mCursor
deallocate mCursor
go
--显示结果
select TableName,RowsCount from TableSpace

--建议使用后两种方式，对于SQL SERVER 2005来说，三种方法都好使，如果是其他板本，可以逐一测试一下。

方法四

--==========================================================================
-- 说明：本脚本用于查询当前中所有表格的记录条数
-- 并将结果存入tableinfo表中，不会删除以备用户再做处理与分析
-- 不过，最后请用户删除此表。
--==========================================================================

代码如下

复制代码

if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[tablespace]) and objectproperty(id, nisusertable) = 1)
drop table [dbo].[tablespace]
go

create table tablespace
(
tablename varchar(20),
rowscount char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
go

declare @sql varchar(500)
declare @tablename varchar(20)

declare cursor1 cursor
for
select name from sysobjects where xtype=u

open cursor1
fetch next from cursor1 into @tablename

while @@fetch_status = 0
begin
set @sql = insert into tablespace
set @sql = @sql + exec sp_spaceused + @tablename +
exec (@sql)
fetch next from cursor1 into @tablename
end
close cursor1
deallocate cursor1
go

--显示结果
select * from tablespace
--order by tablename
--order by tablename asc --按表名称，用于统计表
--order by rowscount desc --按行数量，用于查看表行数
--order by reserved desc, data desc --按占用空间
--order by index_size desc, reserved desc --按索引空间查看
go

--查看库的使用状况,可以随时执行的。
--exec sp_spaceused
--go

我们今天的关于(SqlServer)批量清理指定数据库中所有数据和sql server批量删除数据库的分享就到这里，谢谢您的阅读，如果想了解更多关于mssql sqlserver 使用 sql 脚本清空所有数据库表数据的方法分享、SQL Server SQL 统计一个数据库中所有表记录的数量、SQL Server 得到数据库中所有表的名称及数据条数、SQL Server 查询数据库中所有表数据条数的相关信息，可以在本站进行搜索。

本文标签：