April 3, 2007 at 10:38 am
How to loop through all tables in the databases and truncate them all?
How to loop through all databases and do maintenance on each one and also notify admin on the status on each database (like size,spaceused, spacefree, etc)
April 3, 2007 at 11:17 am
Someone else will have to help you out with the specifics on truncating/specific maintenance, but here's some spaghetti code for looping through databases and tables...
DECLARE @DBName varchar(128)
DECLARE databases CURSOR FOR
SELECT RTRIM( CATALOG_NAME )
FROM INFORMATION_SCHEMA.SCHEMATA
OPEN databases
-- loop through the databases
FETCH NEXT FROM databases INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('
DECLARE @TableName nvarchar (128)
DECLARE tables CURSOR FOR
SELECT sysobjects.name
FROM ' + @DBName + '.dbo.sysobjects sysobjects
WHERE sysobjects.xtype = ''U''
OPEN tables
-- loop through the tables
FETCH NEXT FROM tables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
--you can do whatever you need to the tables here now that you have the table name in a variable
-- loop through the tables
FETCH NEXT FROM tables INTO @TableName
END
CLOSE tables
DEALLOCATE tables
' )
--you can do whatever you need to the databases here now that you have the database name in a variable
-- loop through the databases
FETCH NEXT FROM databases INTO @DBName
END
-- Close and deallocate the cursor
CLOSE databases
DEALLOCATE databases
April 3, 2007 at 1:20 pm
Sounds like suicide but here you go:
if exists (select 1 from tempdb.dbo.sysobjects where name like '%work' and type = 'u')
begin
drop table ##work
end
declare @SQL nvarchar(1000),
@Count int,
@Loop int,
@Name nvarchar(100)
select name,0 process into ##work from sysobjects where type = 'u' order by name
set @Count = (select count(*) from ##work where process = 0)
set @Loop = 1
while @Loop <= @Count
begin
set @Name = (select top 1 name from ##work where process = 0)
set @SQL = 'truncate table '+@Name
exec (@SQL)
set @Loop = @Loop + 1
update ##work
set process = 1
where name = @Name
end
drop table ##work
Actually I have used this before just to clean up a mess.
April 3, 2007 at 1:52 pm
Thanks for the quick reply. I will try that.
April 4, 2007 at 6:17 am
For truncating all of the tables, you are going to either have to drop all foreign keys, or get everything in the correct order before you delete / truncate each table. This is tricky. You may be much better off scripting the entire database and just running the create scripts for all of the objects.
As far as getting all of the row counts, you can loop through each table and count the rows, but if you can live with some possible minor inaccurate row counts, there is a column in the SysIndexes table called "rows" that is updated by SQL server through the database statistics functionality.
You have to pick the correct index to use, but it is not too difficult to figure out.
April 4, 2007 at 7:14 am
I keep this link handy:
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_sp.asp
Two of the undocumented stored procedures are sp_MSforeachdb and sp_MSforeachtable. Both iterate over a list of databases or tables respectively and execute the command text you supply. They each supply a single parameter (database name or table name) which you can use with the ? placeholder.
Check the link for more info, but basically this:
exec
sp_MSforeachdb 'use ?; select ''?'' as database_name, count(*) as tables_count from sysobjects where xtype = ''U'''
will yeild you a list query result sets, one for each of your databases with a count of user tables in each. (Be mindful of the need to escape single quotes).
Similarly,
exec sp_MSforeachtable 'select ''?'' as table_name, count(*) as row_count from ?'
will yeild a list of result sets with table names and their record counts.
Hope this helps!
But boss, why must the urgent always take precedence over the important?
April 4, 2007 at 8:58 am
Another way to do it is generating a list of TRUNCATE TABLE statements. Something like this:
USE YourDatabase
SELECT 'TRUNCATE TABLE '+Name
FROM dbo.SysObjects --- Use Sys.Tables in SQL Server 2005
WHERE Type = 'u' ---If you use Sys.Tables you won't need this clause
Then you run the results and there you go. Of course, as it was said previously, you have to be careful with the order you perform the truncate statements because of the foreign key relationships (if any)
I hope this helps.
April 4, 2007 at 2:37 pm
Thanks everyone for all your help. That was really helpfull and pretty fast.
April 5, 2007 at 9:23 am
Looks like you already have answers but here's something that helps out if you have a ton of FKs. I do what was mentioned earlier by generating statments from sysobjects to truncate the tables but first you'll have to drop all foreign keys and to do so, this helps. It basically creates a table, fills it with alter table staments and loops through executing each row and then drops the table.
CREATE TABLE dropping_constraints
(
cmd VARCHAR(8000)
)
INSERT INTO dropping_constraints
SELECT
'ALTER TABLE [' +
t2.Table_Name +
'] DROP CONSTRAINT ' +
t1.Constraint_Name
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS t1
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t2
ON
t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
--WHERE t2.TABLE_NAME='your_tablename_goes_here'
DECLARE @stmt VARCHAR(8000)
DECLARE @rowcnt INT
SELECT TOP 1 @stmt=cmd FROM dropping_constraints
select @stmt
SET @rowcnt=@@ROWCOUNT
WHILE @rowcnt<>0
BEGIN
EXEC (@stmt)
SET @stmt = 'DELETE FROM dropping_constraints WHERE cmd ='+ QUOTENAME(@stmt,'''')
EXEC (@stmt)
SELECT TOP 1 @stmt=cmd FROM dropping_constraints
SET @rowcnt=@@ROWCOUNT
END
DROP TABLE dropping_constraints
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply