Loop Through every table in a DB

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Chris Morton » Loop Through every table in a DB

Loop Through every table in a DB

Rate Topic

Display Mode

Topic Options

Author

Message

kodracon

Posted Wednesday, January 07, 2009 3:41 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, September 25, 2011 6:50 AM
Points: 55, Visits: 155

Comments posted to this topic are about the item Loop Through every table in a DB

Post #631290

Carlo Romagnano

Posted Tuesday, January 20, 2009 1:08 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 12:56 AM
Points: 1,972, Visits: 1,822

Version for Sql 2000: :D

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--PART OF THE MACRO CODE EXECUTION BY CHRIS MORTON
--CONTACT CBMORTON@GMAIL.COM
--2009-01-05
--20/01/2009 Carlo Romagnano - specify table names and @SQLScript speficy $T as place holder for the table name
-- =============================================
ALTER PROCEDURE x_TableLoops(@table_name sysname = '%',@SQLScript VARCHAR(8000) = NULL)
AS
BEGIN
DECLARE
@tablename sysname
,@Counter INT
,@MaxCounter INT
,@sql varchar(8000)
DECLARE @tables TABLE
(
id INT IDENTITY(1, 1) PRIMARY KEY WITH FILLFACTOR = 100
,[table] sysname
)

INSERT INTO @tables ( [table] )
SELECT TABLE_NAME AS [table]
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE @table_name
SET @MaxCounter = @@ROWCOUNT

SET @sqlscript = ISNULL(@sqlscript,'PRINT ''$T''')
SET @counter = 1
WHILE @counter <= @MaxCounter
BEGIN
SELECT @tablename = [TABLE]
FROM @tables
WHERE id = @counter
SET @sql = REPLACE(@sqlscript,'$T',CAST(@tablename AS VARCHAR(255))
EXEC (@sql)

SET @Counter = @counter + 1
END
END

Post #639702

manorchurch

Posted Tuesday, January 20, 2009 9:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, August 16, 2009 10:04 AM
Points: 2, Visits: 3

What's wrong with declaring a cursor?
-------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[spTableStats] as

/* ---------------------------------------------------------------------------------------------------------
This code, for the current database, lists info about each of its tables:
name, row count, column count, reserved KB, used KB, used percent, category, and description.
Note that the latter 2 are extended properties of each table.
Created by: George Hernandez
Modified: 2001 September 21
*/ ---------------------------------------------------------------------------------------------------------

--Prepare variables and temp table
set nocount on --Don't say how many rows
declare @SQL nvarchar(2000), @TableName sysname, @Columns int, @Rows int
declare @ReservedKB int, @UsedKB int, @UsedPercent real
declare @Category nvarchar(300), @Description nvarchar(300)
if exists(select name from tempdb.dbo.sysobjects where name like '#tbl1%')
drop table #tbl1
create table #tbl1(TableName sysname, Columns int, Rows int,
ReservedKB int, UsedKB int, UsedPercent real, Category nvarchar(300), Description nvarchar(300))

--Fill cursor then populate temp table with columns from extended properties.
declare crsr1 cursor for
select t1.TableName,t1.Columns,t1.Rows,t2.ReservedKB, t2.UsedKB, t2.UsedPercent
from
(select so.name as TableName, count(sc.name) as Columns, si.rowcnt as Rows
from sysobjects as so join syscolumns as sc on (so.id=sc.id)
join sysindexes as si on (so.id=si.id)
where so.type='U' and so.name<>'dtproperties' and si.indid in(0,1)
group by so.name,si.rowcnt) as t1
join
(select so.name as TableName, sum(si.reserved*8) as ReservedKB, sum(si.used*8) as UsedKB
, case sum(si.reserved)
when 0 then 0
else cast(sum(si.used) as real)/cast(sum(si.reserved) as real)*100
end as UsedPercent
from sysobjects as so join sysindexes as si on (so.id=si.id)
where so.type='U' and so.name<>'dtproperties'
group by so.name) as t2 on (t1.TableName=t2.TableName)
--/*
open crsr1
fetch next from crsr1 into @TableName, @Columns, @Rows, @ReservedKB, @UsedKB, @UsedPercent
while @@fetch_status>=0
begin
select @Description=cast(value as nvarchar(300))
from ::fn_listextendedproperty('MS_Description','user','dbo','table',@TableName,null,null)
if @Description is null or @Description='' or @@rowcount=0
select @Description='null'
else
select @Description=quotename(@Description, '''')
select @Category=cast(value as nvarchar(300))
from ::fn_listextendedproperty('Category','user','dbo','table',@TableName,null,null)
if @Category is null or @Category='' or @@rowcount=0
select @Category='null'
else
select @Category=quotename(@Category, '''')
select @SQL=N'insert into #tbl1 '
+'select '
+quotename(@TableName,'''')
+', '+cast(@Columns as nvarchar(50))
+', '+cast(@Rows as nvarchar(50))
+', '+cast(@ReservedKB as nvarchar(50))
+', '+cast(@UsedKB as nvarchar(50))
+', '+cast(@UsedPercent as nvarchar(50))
+', '+@Category
+', '+@Description
-- print @SQL
execute(@SQL)
fetch next from crsr1 into @TableName, @Columns, @Rows, @ReservedKB, @UsedKB, @UsedPercent
end
close crsr1
deallocate crsr1
select * from #tbl1 order by TableName
drop table #tbl1
--*/

Post #640000

RBarryYoung

Posted Tuesday, January 20, 2009 10:22 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

In what way are any of these better than sp_MSforeachtable?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #640044

toniupstny

Posted Wednesday, January 21, 2009 12:19 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:04 AM
Points: 241, Visits: 928

The ms_foreach.. proc is an unofficial method and can be removed at the will of Bill et al. There are also cursors in the ms_foreach... procs, though unless you get to humongous numbers of tables and lengthy statements, they might be okay.

The solutions with the Loops are most likely better than the cursor - no?

Toni

Post #641042

RBarryYoung

Posted Wednesday, January 21, 2009 12:24 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

toniupstny (1/21/2009)

No. Loops and cursors are pretty much the same things with similar problems with respect to performance.

And yes, "sp_MSforeachtable" is undocumented, but it does work and is still in 2000, 2005 and 2008. And it has more features than any of these.

And by SQL 2005, you do not need any of the cursors/loop techniques anymore.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #641050

Jeff Moden

Posted Wednesday, January 21, 2009 1:05 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910, Visits: 26,800

RBarryYoung (1/21/2009)

toniupstny (1/21/2009)

To further that, a good ol' "firehose" cursor is just as fast as a While Loop and uses about the same number of resources.

So far as using undocumented features goes because they might change it... I've got news for you... they change documented features without warning, as well. Take, for example, when they changed the privs on sp_MakeWebTask in 2k sp4... it caught a lot of people very flat footed and broke a lot of code. Heh... use what you can, can what you can't. ;)

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #641096

« Prev Topic | Next Topic »

Permissions