blocks|key|69730|text|这里有一个简单得多的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|69731|declare+@SQL+nvarchar(MAX)+=+''

SELECT+@SQL+=+@SQL+%2B+'truncate+table+'%2B+t.TABLE_NAME+%2B+';'+
FROM+sysindexes+i
join+INFORMATION_SCHEMA.TABLES+t+on+t.TABLE_NAME+=+object_name(i.id)
WHERE+t.TABLE_TYPE+=+'BASE+TABLE'+
++++AND+t.TABLE_NAME+NOT+IN+('Test')
GROUP+BY+t.TABLE_SCHEMA
++++,+t.TABLE_NAME
HAVING+MAX(i.rows)+>+0

select+@SQL
--exec+sp_executesql+@SQL|code-block|syntax|javascript|69732|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is a much simpler approach to this. This is still extremely brittle and highly likely to break in...well....just about every database everywhere because you can't truncate a table if there is a foreign key to it.

<pre><code>declare @SQL nvarchar(MAX) = ''

SELECT @SQL = @SQL + 'truncate table '+ t.TABLE_NAME + ';' 
FROM sysindexes i
join INFORMATION_SCHEMA.TABLES t on t.TABLE_NAME = object_name(i.id)
WHERE t.TABLE_TYPE = 'BASE TABLE' 
 AND t.TABLE_NAME NOT IN ('Test')
GROUP BY t.TABLE_SCHEMA
 , t.TABLE_NAME
HAVING MAX(i.rows) &gt; 0

select @SQL
--exec sp_executesql @SQL
</code></pre>

blocks|key|71062|text|不幸的是，我想不起来我是在哪里找到proc的。在脚本中使用它来清除测试数据库中的数据。在表被截断后，它会记住并重新创建FK。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|71063|CREATE+PROCEDURE+[dbo].[truncate_non_empty_table]

++@TableToTruncate+++++++++++++++++VARCHAR(64)

AS+

BEGIN

SET+NOCOUNT+ON

--+GLOBAL+VARIABLES
DECLARE+@i+int
DECLARE+@Debug+bit
DECLARE+@Recycle+bit
DECLARE+@Verbose+bit
DECLARE+@TableName+varchar(80)
DECLARE+@ColumnName+varchar(80)
DECLARE+@ReferencedTableName+varchar(80)
DECLARE+@ReferencedColumnName+varchar(80)
DECLARE+@ConstraintName+varchar(250)

DECLARE+@CreateStatement+varchar(max)
DECLARE+@DropStatement+varchar(max)+++
DECLARE+@TruncateStatement+varchar(max)
DECLARE+@CreateStatementTemp+varchar(max)
DECLARE+@DropStatementTemp+varchar(max)
DECLARE+@TruncateStatementTemp+varchar(max)
DECLARE+@Statement+varchar(max)

++++++++--+1+=+Will+not+execute+statements+
+SET+@Debug+=+0
++++++++--+0+=+Will+not+create+or+truncate+storage+table
++++++++--+1+=+Will+create+or+truncate+storage+table
+SET+@Recycle+=+0
++++++++--+1+=+Will+print+a+message+on+every+step
+set+@Verbose+=+1

+SET+@i+=+1
++++SET+@CreateStatement+=+'ALTER+TABLE+[dbo].[<tablename>]++WITH+NOCHECK+ADD++CONSTRAINT+[<constraintname>]+FOREIGN+KEY([<column>])+REFERENCES+[dbo].[<reftable>]+([<refcolumn>])'
++++SET+@DropStatement+=+'ALTER+TABLE+[dbo].[<tablename>]+DROP+CONSTRAINT+[<constraintname>]'
++++SET+@TruncateStatement+=+'TRUNCATE+TABLE+[<tablename>]'

--+Drop+Temporary+tables

IF+OBJECT_ID('tempdb..#FKs')+IS+NOT+NULL
++++DROP+TABLE+#FKs

--+GET+FKs
SELECT+ROW_NUMBER()+OVER+(ORDER+BY+OBJECT_NAME(parent_object_id),+clm1.name)+as+ID,
+++++++OBJECT_NAME(constraint_object_id)+as+ConstraintName,
+++++++OBJECT_NAME(parent_object_id)+as+TableName,
+++++++clm1.name+as+ColumnName,+
+++++++OBJECT_NAME(referenced_object_id)+as+ReferencedTableName,
+++++++clm2.name+as+ReferencedColumnName
++INTO+#FKs
++FROM+sys.foreign_key_columns+fk
+++++++JOIN+sys.columns+clm1+
+++++++++ON+fk.parent_column_id+=+clm1.column_id+
++++++++++++AND+fk.parent_object_id+=+clm1.object_id
+++++++JOIN+sys.columns+clm2
+++++++++ON+fk.referenced_column_id+=+clm2.column_id+
++++++++++++AND+fk.referenced_object_id=+clm2.object_id
+--WHERE+OBJECT_NAME(parent_object_id)+not+in+('//tables+that+you+do+not+wont+to+be+truncated')
+WHERE+OBJECT_NAME(referenced_object_id)+=+@TableToTruncate
+ORDER+BY+OBJECT_NAME(parent_object_id)


--+Prepare+Storage+Table
IF+Not+EXISTS(SELECT+1+FROM+INFORMATION_SCHEMA.TABLES+WHERE+TABLE_NAME+=+'Internal_FK_Definition_Storage')
+++BEGIN
++++++++IF+@Verbose+=+1
+++++PRINT+'1.+Creating+Process+Specific+Tables...'

++--+CREATE+STORAGE+TABLE+IF+IT+DOES+NOT+EXISTS
++CREATE+TABLE+[Internal_FK_Definition_Storage]+
++(
+++ID+int+not+null+identity(1,1)+primary+key,
+++FK_Name+varchar(250)+not+null,
+++FK_CreationStatement+varchar(max)+not+null,
+++FK_DestructionStatement+varchar(max)+not+null,
+++Table_TruncationStatement+varchar(max)+not+null
++)+
+++END+
ELSE
+++BEGIN
++++++++IF+@Recycle+=+0
++++++++++++BEGIN
++++++++++++++++IF+@Verbose+=+1
+++++++PRINT+'1.+Truncating+Process+Specific+Tables...'

++++--+TRUNCATE+TABLE+IF+IT+ALREADY+EXISTS
++++TRUNCATE+TABLE+[Internal_FK_Definition_Storage]++++
++++++END
++++++ELSE
+++++++++PRINT+'1.+Process+specific+table+will+be+recycled+from+previous+execution...'
+++END


IF+@Recycle+=+0
+++BEGIN

++IF+@Verbose+=+1
+++++PRINT+'2.+Backing+up+Foreign+Key+Definitions...'

++--+Fetch+and+persist+FKs+++++++++++++
++WHILE+(@i+<=+(SELECT+MAX(ID)+FROM+#FKs))
+++BEGIN
++++SET+@ConstraintName+=+(SELECT+ConstraintName+FROM+#FKs+WHERE+ID+=+@i)
++++SET+@TableName+=+(SELECT+TableName+FROM+#FKs+WHERE+ID+=+@i)
++++SET+@ColumnName+=+(SELECT+ColumnName+FROM+#FKs+WHERE+ID+=+@i)
++++SET+@ReferencedTableName+=+(SELECT+ReferencedTableName+FROM+#FKs+WHERE+ID+=+@i)
++++SET+@ReferencedColumnName+=+(SELECT+ReferencedColumnName+FROM+#FKs+WHERE+ID+=+@i)

++++SET+@DropStatementTemp+=+REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
++++SET+@CreateStatementTemp+=+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
++++SET+@TruncateStatementTemp+=+REPLACE(@TruncateStatement,'<tablename>',@TableName)+

++++INSERT+INTO+[Internal_FK_Definition_Storage]
++++++++++++++++++++++++SELECT+@ConstraintName,+@CreateStatementTemp,+@DropStatementTemp,+@TruncateStatementTemp

++++SET+@i+=+@i+%2B+1

++++IF+@Verbose+=+1
+++++++PRINT+'++>+Backing+up+['+%2B+@ConstraintName+%2B+']+from+['+%2B+@TableName+%2B+']'

++++END+++
++++END+++
++++ELSE+
+++++++PRINT+'2.+Backup+up+was+recycled+from+previous+execution...'

+++++++IF+@Verbose+=+1
+++++PRINT+'3.+Dropping+Foreign+Keys...'

++++--+DROP+FOREING+KEYS
++++SET+@i+=+1
++++WHILE+(@i+<=+(SELECT+MAX(ID)+FROM+[Internal_FK_Definition_Storage]))
++++++++++BEGIN
+++++++++++++SET+@ConstraintName+=+(SELECT+FK_Name+FROM+[Internal_FK_Definition_Storage]+WHERE+ID+=+@i)
++++SET+@Statement+=+(SELECT+FK_DestructionStatement+FROM+[Internal_FK_Definition_Storage]+WITH+(NOLOCK)+WHERE+ID+=+@i)

++++IF+@Debug+=+1+
+++++++PRINT+@Statement
++++ELSE
+++++++EXEC(@Statement)

++++SET+@i+=+@i+%2B+1


++++IF+@Verbose+=+1
+++++++PRINT+'++>+Dropping+['+%2B+@ConstraintName+%2B+']'

+++++++++++++END+++++


++++IF+@Verbose+=+1
+++++++PRINT+'4.+Truncating+Tables...'

++++--+TRUNCATE+TABLES
--+SzP:+commented+out+as+the+tables+to+be+truncated+might+also+contain+tables+that+has+foreign+keys
--+to+resolve+this+the+stored+procedure+should+be+called+recursively,+but+I+dont+have+the+time+to+do+it...++++++++++
+/*
++++SET+@i+=+1
++++WHILE+(@i+<=+(SELECT+MAX(ID)+FROM+[Internal_FK_Definition_Storage]))
++++++++++BEGIN

++++SET+@Statement+=+(SELECT+Table_TruncationStatement+FROM+[Internal_FK_Definition_Storage]+WHERE+ID+=+@i)

++++IF+@Debug+=+1+
+++++++PRINT+@Statement
++++ELSE
+++++++EXEC(@Statement)

++++SET+@i+=+@i+%2B+1

++++IF+@Verbose+=+1
+++++++PRINT+'++>+'+%2B+@Statement
++++++++++END
*/++++++++++


++++IF+@Verbose+=+1
+++++++PRINT+'++>+TRUNCATE+TABLE+['+%2B+@TableToTruncate+%2B+']'

++++IF+@Debug+=+1+
++++++++PRINT+'TRUNCATE+TABLE+['+%2B+@TableToTruncate+%2B+']'
++++ELSE
++++++++EXEC('TRUNCATE+TABLE+['+%2B+@TableToTruncate+%2B+']')


++++IF+@Verbose+=+1
+++++++PRINT+'5.+Re-creating+Foreign+Keys...'

++++--+CREATE+FOREING+KEYS
++++SET+@i+=+1
++++WHILE+(@i+<=+(SELECT+MAX(ID)+FROM+[Internal_FK_Definition_Storage]))
++++++++++BEGIN
+++++++++++++SET+@ConstraintName+=+(SELECT+FK_Name+FROM+[Internal_FK_Definition_Storage]+WHERE+ID+=+@i)
++++SET+@Statement+=+(SELECT+FK_CreationStatement+FROM+[Internal_FK_Definition_Storage]+WHERE+ID+=+@i)

++++IF+@Debug+=+1+
+++++++PRINT+@Statement
++++ELSE
+++++++EXEC(@Statement)

++++SET+@i+=+@i+%2B+1


++++IF+@Verbose+=+1
+++++++PRINT+'++>+Re-creating+['+%2B+@ConstraintName+%2B+']'

++++++++++END

++++IF+@Verbose+=+1
+++++++PRINT+'6.+Process+Completed'


END|code-block|syntax|javascript|71064|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Unfortunately, I can't recall where I found the proc. Used it in a script to clean data from test db. It remembers and recreates FKs after a table is truncated.

<pre><code>CREATE PROCEDURE [dbo].[truncate_non_empty_table]

 @TableToTruncate VARCHAR(64)

AS 

BEGIN

SET NOCOUNT ON

-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)

DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max) 
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

 -- 1 = Will not execute statements 
 SET @Debug = 0
 -- 0 = Will not create or truncate storage table
 -- 1 = Will create or truncate storage table
 SET @Recycle = 0
 -- 1 = Will print a message on every step
 set @Verbose = 1

 SET @i = 1
 SET @CreateStatement = 'ALTER TABLE [dbo].[&lt;tablename&gt;] WITH NOCHECK ADD CONSTRAINT [&lt;constraintname&gt;] FOREIGN KEY([&lt;column&gt;]) REFERENCES [dbo].[&lt;reftable&gt;] ([&lt;refcolumn&gt;])'
 SET @DropStatement = 'ALTER TABLE [dbo].[&lt;tablename&gt;] DROP CONSTRAINT [&lt;constraintname&gt;]'
 SET @TruncateStatement = 'TRUNCATE TABLE [&lt;tablename&gt;]'

-- Drop Temporary tables

IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
 DROP TABLE #FKs

-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
 OBJECT_NAME(constraint_object_id) as ConstraintName,
 OBJECT_NAME(parent_object_id) as TableName,
 clm1.name as ColumnName, 
 OBJECT_NAME(referenced_object_id) as ReferencedTableName,
 clm2.name as ReferencedColumnName
 INTO #FKs
 FROM sys.foreign_key_columns fk
 JOIN sys.columns clm1 
 ON fk.parent_column_id = clm1.column_id 
 AND fk.parent_object_id = clm1.object_id
 JOIN sys.columns clm2
 ON fk.referenced_column_id = clm2.column_id 
 AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
 WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
 ORDER BY OBJECT_NAME(parent_object_id)


-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
 BEGIN
 IF @Verbose = 1
 PRINT '1. Creating Process Specific Tables...'

 -- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
 CREATE TABLE [Internal_FK_Definition_Storage] 
 (
 ID int not null identity(1,1) primary key,
 FK_Name varchar(250) not null,
 FK_CreationStatement varchar(max) not null,
 FK_DestructionStatement varchar(max) not null,
 Table_TruncationStatement varchar(max) not null
 ) 
 END 
ELSE
 BEGIN
 IF @Recycle = 0
 BEGIN
 IF @Verbose = 1
 PRINT '1. Truncating Process Specific Tables...'

 -- TRUNCATE TABLE IF IT ALREADY EXISTS
 TRUNCATE TABLE [Internal_FK_Definition_Storage] 
 END
 ELSE
 PRINT '1. Process specific table will be recycled from previous execution...'
 END


IF @Recycle = 0
 BEGIN

 IF @Verbose = 1
 PRINT '2. Backing up Foreign Key Definitions...'

 -- Fetch and persist FKs 
 WHILE (@i &lt;= (SELECT MAX(ID) FROM #FKs))
 BEGIN
 SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
 SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
 SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
 SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
 SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)

 SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'&lt;tablename&gt;',@TableName),'&lt;constraintname&gt;',@ConstraintName)
 SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'&lt;tablename&gt;',@TableName),'&lt;column&gt;',@ColumnName),'&lt;constraintname&gt;',@ConstraintName),'&lt;reftable&gt;',@ReferencedTableName),'&lt;refcolumn&gt;',@ReferencedColumnName)
 SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'&lt;tablename&gt;',@TableName) 

 INSERT INTO [Internal_FK_Definition_Storage]
 SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp

 SET @i = @i + 1

 IF @Verbose = 1
 PRINT ' &gt; Backing up [' + @ConstraintName + '] from [' + @TableName + ']'

 END 
 END 
 ELSE 
 PRINT '2. Backup up was recycled from previous execution...'

 IF @Verbose = 1
 PRINT '3. Dropping Foreign Keys...'

 -- DROP FOREING KEYS
 SET @i = 1
 WHILE (@i &lt;= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
 BEGIN
 SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
 SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)

 IF @Debug = 1 
 PRINT @Statement
 ELSE
 EXEC(@Statement)

 SET @i = @i + 1


 IF @Verbose = 1
 PRINT ' &gt; Dropping [' + @ConstraintName + ']'

 END 


 IF @Verbose = 1
 PRINT '4. Truncating Tables...'

 -- TRUNCATE TABLES
-- SzP: commented out as the tables to be truncated might also contain tables that has foreign keys
-- to resolve this the stored procedure should be called recursively, but I dont have the time to do it... 
 /*
 SET @i = 1
 WHILE (@i &lt;= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
 BEGIN

 SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

 IF @Debug = 1 
 PRINT @Statement
 ELSE
 EXEC(@Statement)

 SET @i = @i + 1

 IF @Verbose = 1
 PRINT ' &gt; ' + @Statement
 END
*/ 


 IF @Verbose = 1
 PRINT ' &gt; TRUNCATE TABLE [' + @TableToTruncate + ']'

 IF @Debug = 1 
 PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
 ELSE
 EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')


 IF @Verbose = 1
 PRINT '5. Re-creating Foreign Keys...'

 -- CREATE FOREING KEYS
 SET @i = 1
 WHILE (@i &lt;= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
 BEGIN
 SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
 SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

 IF @Debug = 1 
 PRINT @Statement
 ELSE
 EXEC(@Statement)

 SET @i = @i + 1


 IF @Verbose = 1
 PRINT ' &gt; Re-creating [' + @ConstraintName + ']'

 END

 IF @Verbose = 1
 PRINT '6. Process Completed'


END
</code></pre>

<pre><code>DECLARE @sql VARCHAR(MAX)

SELECT @sql = X.[text()]
FROM
 (SELECT 'truncate table '+ t.TABLE_NAME + ';' AS [text()]
 FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
 WHERE t.TABLE_NAME = object_name(i.id)
 AND t.TABLE_TYPE = 'BASE TABLE' 
 AND t.TABLE_NAME NOT IN ('Test')
 GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
 HAVING MAX(i.rows) &gt; 0
 FOR XML PATH ('')) X

exec(@sql)
</code></pre>

I am trying to create a script like the above as in <a href="https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv">this question</a> but I am getting:

<blockquote>
 Msg 8155, Level 16, State 2, Line 12 
 No column name was specified for column 1 of 'X'. 
 
 Msg 207, Level 16, State 1, Line 2 
 Invalid column name 'text()'
</blockquote>

How to truncate SQL Server tables that are not empty?

数据库

DECLARE @sql VARCHAR(MAX)SELECT @sql = X.[text()]FROM    (SELECT 'truncate table '+ t.TABLE_NAME + ';' AS [text()]     FROM sysindexes i, INFORMATION_SCHEMA.TAB...

问如何截断非空的SQL Server表？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何截断非空的SQL Server表？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何截断非空的SQL Server表？
EN