Truncate tables only with on schema

Question

I'm looking for a way to truncate all tables on one schema only. I'm getting the list of all tables on that schema:

*SELECT name, object_id FROM sys.objects WHERE schema_id = (SELECT SCHEMA_ID('seg'))*

but I'm struggling to find a way to TRUNCATE all from the result set.

thanks for any points!

score 2 · Accepted Answer · edited May 23 '17 at 12:28

2

Refer to Link OR try this script

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL = (
    SELECT 'TRUNCATE TABLE [' + s.name + '].[' + o.name + ']' + CHAR(13)
    FROM sys.objects o WITH (NOWAIT)
    JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = 'U'
        AND s.name = 'dbo'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL

--EXEC sys.sp_executesql @SQL

edited May 23 '17 at 12:28

Community

1
1

answered Feb 06 '14 at 07:06

Naveen Kumar

1,541
10
12

Thanks Naveen! I've been also testing EXEC sp_msforeachtable @command1 ='TRUNCATE TABLE ?' ,@whereand = ' And Object_id In (SELECT object_id FROM sys.objects WHERE schema_id = 6)' – Thomas Pollack Feb 06 '14 at 07:14

score 1 · Answer 2 · edited Jun 15 '18 at 10:36

1

I also found the use of msforeachtable...if anyone would prefer :)

EXEC sp_msforeachtable
       @command1 ='TRUNCATE TABLE ?'
      ,@whereand = ' And Object_id In (SELECT object_id FROM sys.objects WHERE 
                                       schema_id = 6)'

edited Jun 15 '18 at 10:36

codingbadger

42,678
13
95
110

answered Feb 07 '14 at 01:57

Thomas Pollack

59
2
11

Truncate tables only with on schema

2 Answers2