How to Exclude Tables from Sp_Msforeachtable

How to exclude tables from sp_msforeachtable

EXEC sp_MSforeachtable 'IF OBJECT_ID(''?'') NOT IN (
                                                    ISNULL(OBJECT_ID(''[dbo].[T1]''),0),
                                                    ISNULL(OBJECT_ID(''[dbo].[T2]''),0)
                                                   )
                        DELETE FROM ?'

How to delete data in all tables from a database except except few tables

Use IF ''?'' NOT IN (''TABLE1'',''TABLE2'') before your DELETE FROM [?]

By the way, I suggest using TRUNCATE TABLE [?] instead of DELETE FROM [?]

sp_MSforeachtable skipping certain tablenames using like

From raresql

create table #counts 
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
, @whereand = ' And Object_id In (Select Object_id From sys.objects
Where name not like ''xxx%'')'
SELECT table_name, row_count FROM #counts

Delete Data from all tables in Sqlserver Database except some tables

Try to do it another way:

DECLARE @command nvarchar(max); 

--Remove spaces in front of ,ISNULL 
SELECT @command = N'IF OBJECT_ID(''?'') NOT IN (
 ISNULL(OBJECT_ID(''[dbo].[T1]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T2]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T3]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T4]''),0)
,ISNULL(OBJECT_ID(''[dbo].[T5]''),0)
...
,ISNULL(OBJECT_ID(''[dbo].[TN]''),0)
)
DELETE FROM ?'; 

EXEC sp_MSforeachtable @command;

NOTE: This SP works only with nvarchar(2000) in first command (source).

It is the first command to be executed by this Stored Procedure and the data type is nvarchar(2000).

Remove all tables from schema except specific ones in SQL?

If you want to keep using sp_msforeachtable, pass in the set of tables names to keep using a temp table. Here's an example using the boo schema:

create schema boo;
create table boo.t(i int);

create table #keep (name sysname); 
insert #keep values ('myFirsttable'), ('mySecondTable'), ('myThirdTable');

exec sp_msforeachtable 
   @command1='drop table ?; print ''dropped ?''',
   @whereand = 'and schema_name(schema_id) = ''your_schema_name'' and object_name(object_id) not in (select name from #keep)';

But personally, I'd probably just write my own stored procedure with a cursor. It's harder to mess up.

Note that this solution expects you to put the tables you want to keep into the temp table. Charlieface's solution expects you to put the names of tables you want to drop into the table variable.

Drop all constraints from tables with a certain prefix

Since you mentioned already having the scripts required to regenerate the foreign keys, here's a way to generate and execute the drops. You might want to add some error handling.

declare @sql varchar(max);

declare c cursor local fast_forward for 
   select   concat
            (
               'alter table ',
               quotename(s.name), 
               '.', 
               quotename(t.name),
               ' drop constraint ',
               quotename(fk.name)
            )
   from     sys.foreign_keys  fk
   join     sys.tables        t  on t.object_id = fk.parent_object_id
   join     sys.schemas       s  on t.schema_id = s.schema_id
   where    t.is_ms_shipped = 0
            and t.name like 'myprefix%';

open c;
fetch next from c into @sql;
while (@@fetch_status = 0) 
begin   
   print @sql;
   --exec(@sql); uncomment after checking output and run again (or just run output manually)
   fetch next from c into @sql;
end

SQL Server sp_msforeachtable usage to select only those tables which meet some condition

You know how sp_MSforeachtable is undocumented, and may go away at any time/be modified?

Well, if you're happy to ignore that, it has another parameter called @whereand, which is appended to the WHERE clause of the internal query that is being used to find the tables (and should start with an AND).

You also have to know that there's an alias, o against sysobjects, and a second alias syso against sys.all_objects.

Using this knowledge, you might craft your @whereand parameter as:

EXEC sp_MSforeachtable 
@command1='...',
@whereand='AND o.id in (select object_id from sys.columns c where c.name=''EMP_CODE'')'

You can now also simplify your command1, since you know it will only be run against tables containing an EMP_CODE column. I'd probably take out the COUNT(*) condition also, since I don't see what value it's adding.

---

Updated based on your further work, and tested against one table:

DECLARE @EMPCODE AS VARCHAR(20)
SET @EMPCODE='HO081'
declare @sql nvarchar(2000)
set @sql = '
    DECLARE @COUNT AS INT
    SELECT @COUNT=COUNT(*) FROM ? WHERE EMP_CODE='''+@EMPCODE+'''
    IF @COUNT>0
    BEGIN
        PRINT PARSENAME("?",1)+'' => ''+CONVERT(VARCHAR,@COUNT)+'' ROW(S)''
        --PRINT ''DELETE FROM ''+PARSENAME("?",1)+'' WHERE EMP_CODE='''''+@EMPCODE+'''''''
    END
'
EXEC sp_MSforeachtable 
@command1=@sql,@whereand='AND O.ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS C WHERE C.NAME=''EMP_CODE'')'

(I've reverted the @whereand to query for EMP_CODE, since you don't want to replace the value there).

The issue is that, you can pass parameters to a stored procedure, or literals, but you can't perform calculations/combining actions between them - so I moved the construction of the sql statement out into a separate action.

---

Related Topics