<< 用 SQL 語法查詢符合條件範圍的 IP | 回blog首頁 | ASP.Net 2.0 YUI Style UpdateProgress >>

2007年11月15日

SQL Server 2005 - Truncate All Tables

Truncate All Tables - Part II by  Madhivanan

  1. SET NoCount ON
  3. DECLARE @tableName varchar(200)
  4. SET @tableName=''
  6. WHILE EXISTS
  7. (
  8.   --Find all child tables and those which have no relations
  9.   SELECT T.table_name
  10.   FROM INFORMATION_SCHEMA.TABLES T
  11.        LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
  12.   ON T.table_name = TC.table_name
  13.   WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )
  14.        AND T.table_name NOT IN ('dtproperties', 'sysconstraints', 'syssegments')
  15.        AND Table_type = 'BASE TABLE'
  16.        AND T.table_name > @TableName
  17. )
  19. Begin
  20.   SELECT @tableName = min(T.table_name)
  21.   FROM INFORMATION_SCHEMA.TABLES T
  22.        LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
  23.   ON T.table_name=TC.table_name
  24.   WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )
  25.        AND T.table_name NOT IN ('dtproperties', 'sysconstraints', 'syssegments')
  26.        AND Table_type = 'BASE TABLE'
  27.        AND T.table_name > @TableName
  29.   --Truncate the table
  30.   Exec('Truncate table '+@tableName)
  31. End
  33. SET @TableName=''
  35. WHILE EXISTS
  36. (
  37.   --Find all Parent tables
  38.   SELECT T.table_name
  39.   FROM INFORMATION_SCHEMA.TABLES T
  40.        LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
  41.   ON T.table_name = TC.table_name
  42.   WHERE TC.constraint_Type = 'Primary Key'
  43.        AND T.table_name <> 'dtproperties'
  44.        AND Table_type='BASE TABLE'
  45.        AND T.table_name > @TableName 
  46. )
  48. Begin
  49.   SELECT @tableName = min(T.table_name)
  50.   FROM INFORMATION_SCHEMA.TABLES T
  51.        LEFT OUTER JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
  52.   ON T.table_name=TC.table_name
  53.   WHERE TC.constraint_Type = 'Primary Key'
  54.        AND T.table_name <> 'dtproperties'
  55.        AND Table_type = 'BASE TABLE'
  56.        AND T.table_name > @TableName
  58.   --Delete the table
  59.   Exec('Delete from '+@tableName)
  61.   --Reset identity column
  62. IF EXISTS
  63. (
  64.    SELECT *
  65.    FROM INFORMATION_SCHEMA.COLUMNS
  66.    WHERE COLUMNPROPERTY(
  67.     OBJECT_ID( QUOTENAME(table_schema)+ '.' + QUOTENAME(@tableName) ),
  68.     column_name,'IsIdentity'
  69.    ) = 1
  70. )
  72. DBCC CHECKIDENT (@tableName, RESEED, 1)
  73. End
  75. SET NoCount OFF


Posted by chhuang0123 at 樂多Roodo! │11:25 │回應(0) │引用(0) │ADO.NET
樂多分類：網路/3C 工具：編輯本文
Ads by Roodo! 

引用URL

http://cgi.blog.roodo.com/trackback/4483435
  


回最上方