Loop Through every table in a DB

  • kodracon

    Ten Centuries

    Points: 1027

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

  • Carlo Romagnano

    SSC-Insane

    Points: 21713

    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

  • manorchurch

    SSC Rookie

    Points: 36

    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

    SSC Guru

    Points: 143327

    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]

  • toniupstny

    Hall of Fame

    Points: 3986

    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

    SSC Guru

    Points: 143327

    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]

  • Jeff Moden

    SSC Guru

    Points: 994281

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply