Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Loop Through every table in a DB


Loop Through every table in a DB

Author
Message
kodracon
kodracon
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 162
Comments posted to this topic are about the item Loop Through every table in a DB
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3614 Visits: 3236
Version for Sql 2000: BigGrin

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
manorchurch
manorchurch
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
--*/
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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."
toniupstny
toniupstny
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 940
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
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
toniupstny (1/21/2009)
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

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."
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45127 Visits: 39923
RBarryYoung (1/21/2009)
toniupstny (1/21/2009)
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

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.


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. Wink

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search