Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Loop Through every table in a DB Expand / Collapse
Author
Message
Posted Wednesday, January 7, 2009 3:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
Comments posted to this topic are about the item Loop Through every table in a DB
Post #631290
Posted Tuesday, January 20, 2009 1:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:59 AM
Points: 2,532, Visits: 2,404
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
Posted Tuesday, January 20, 2009 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, January 20, 2009 10:22 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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
Posted Wednesday, January 21, 2009 12:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:51 AM
Points: 242, 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
Post #641042
Posted Wednesday, January 21, 2009 12:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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."
Post #641050
Posted Wednesday, January 21, 2009 1:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:46 PM
Points: 35,218, Visits: 31,676
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. ;)


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #641096
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse