January 7, 2009 at 3:41 am
Comments posted to this topic are about the item Loop Through every table in a DB
January 20, 2009 at 1:08 am
Version for Sql 2000: 😀
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
,
sysname
)
INSERT INTO @tables (
)
SELECT TABLE_NAME AS
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 =
FROM @tables
WHERE id = @counter
SET @sql = REPLACE(@sqlscript,'$T',CAST(@tablename AS VARCHAR(255))
EXEC (@sql)
SET @Counter = @counter + 1
END
END
January 20, 2009 at 9:14 am
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
--*/
January 20, 2009 at 10:22 am
In what way are any of these better than sp_MSforeachtable?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 21, 2009 at 12:19 pm
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
January 21, 2009 at 12:24 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 21, 2009 at 1:05 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply