1

I have different types of schema in my database. If I want to delete the tables with staging schema, how can I modify this stored procedure ?

CREATE PROC sp_Truncate
AS
BEGIN
    EXEC sp_MSforeachtable @command1 = 'TRUNCATE TABLE ?'
END
GO

Is there any way I can pass a parameter (type varchar or nvarchar) for the schema name ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Justin
  • 393
  • 1
  • 7
  • 21
  • In general you can qualify table name with schema name, so instead of just giving the simple table name, include schema name, like "schema.table" – DBug Nov 22 '15 at 15:06
  • `sp_MSforeachtable` and `sp_MSforeachdb` are notorious for missing tables and databases. Use `sys` cataloge views as suggested in `MM93` answer. – M.Ali Nov 22 '15 at 15:10
  • `DELETE`, `DROP` or `TRUNCATE`: which do you really want? – HABO Nov 22 '15 at 15:14
  • Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Nov 22 '15 at 15:50

2 Answers2

5

It is safe to use Delete statement without where clause instead of truncate which may fail if you have referential constraints. Try this query

Declare @sql Nvarchar(max)=''

SELECT @sql += ' delete from ' + s.NAME + '.' + t.NAME 
              +' Go DBCC CHECKIDENT ('''+s.NAME + '''.''' + t.NAME+''', RESEED, 0) GO '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U' 
and s.name = 'schema_name' -- your schema name

Exec sp_executesql @sql

Cursor Version:

DECLARE @s_name VARCHAR(128)
DECLARE @t_name VARCHAR(128) 

DECLARE db_cursor CURSOR FOR  
SELECT  s.NAME as  Schema_Nam , t.NAME as Table_Name
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U' 
and s.name = 'schema_name'   

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @s_name,@t_name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       set @sql += ' delete from ' + s.NAME + '.' + t.NAME 
                  +' Go DBCC CHECKIDENT ('''+s.NAME + '''.''' + t.NAME+''', RESEED, 0) GO '   
FETCH NEXT FROM db_cursor INTO @s_name,@t_name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Exec sp_executesql @sql
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

You can use (keep in mind that sp_msforeachtable is undocummented stored procedure):

EXEC Sp_msforeachtable 
    @command1='Truncate Table ?', @whereand='and Schema_Id=Schema_id(''SchemaName'')';

SqlFiddleDemo

sp_msforeachtable parameters:

@command1 - is the first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)

@whereand - this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000);

Warning:

I assume your staging area tables don't have foreign keys. Otherwise it may fail.

Do not name stored procedures with sp_ prefix:

In SQL Server, the sp_ prefix designates system stored procedures. If you use that prefix for your stored procedures, the name of your procedure might conflict with the name of a system stored procedure that will be created in the future. If such a conflict occurs, your application might break if your application refers to the procedure without qualifying the reference by schema. In this situation, the name will bind to the system procedure instead of to your procedure.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275