Working With System Tables

  • Alex Grinberg-230981

    SSC Eights!

    Points: 897

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aGrinberg/workingwithsystemtables.asp

  • webtekkie

    SSCommitted

    Points: 1901

    If anybody hasn't seen the MS system table map before, you NEED to download it now.  Trust me, you will swap your Granny for it!!

    http://download.microsoft.com/download/SQLSVR2000/sysmap/2000/WIN98MeXP/EN-US/systbl.chm

  • SeaQL

    SSC Eights!

    Points: 947

    I've been using "2. Get row count from all Tables" for some time now to monitor the size of tables and their changes over time.  Today, I noticed that some of the numbers on our system do not match up.

    Using the "2. Get row count from all Tables" script I receive:

    table name - 510312 records

    When I run: "select count(*) from tablename" I receive

    510542 records

    This has me puzzled.  I thought the two outcomes should be the same.  Would anyone have a solution about this? Has anyone experienced anything similar?  Thanks.

    Jan S.

  • fai0626

    Mr or Mrs. 500

    Points: 522

    Hi there,

    After you run the sp_spaceused stored procedure, it will show the correct rowcount.

  • Jeff Scott-375773

    SSC Enthusiast

    Points: 144

    Here's what I think is a little upgrade to #6. This sp reduces human interaction to 1 step, but completes the entire search before showing you the results... so pick your poison

    ALTER PROCEDURE [dbo].[sp_SearchTables] (

    @TextPart varchar(1000)

    )

    AS

    set nocount on

    DECLARE @data table (

    ind int identity(1,1),

    query varchar(1000)

    )

    DECLARE @count int

    DECLARE @iRow int

    DECLARE @sql varchar(1000)

    CREATE table #output (

    Context varchar(255) null,

    ColumnName varchar(255) null,

    TableName varchar(255) null

    )

    INSERT INTO @data

    select 'IF EXISTS(select [' + c.name + '] from [' + o.name

    + '] where [' + c.name + '] like ''%' + @TextPart + '%'')' + CHAR(13) +

    'insert into #output '

    +' select ' + c.name +',''' + c.name + ''',''' + o.name +''' from ' + o.name + ' where '

    + c.name + ' like ''%' + @TextPart + '%''' + CHAR(13) + CHAR(13)

    from syscolumns c

    inner join systypes t on t.xtype=c.xtype

    inner join sysobjects o on o.id = c.id

    where o.type = 'u' and c.id > 500 and t.name in ('varchar', 'char')

    order by 1

    SET @count = @@ROWCOUNT

    SET @iRow = 1

    WHILE @iRow <= @count

    BEGIN

    select @sql = query from @data where ind = @iRow

    exec(@sql)

    SET @iRow = @iRow + 1

    END

    select * from #output

    drop table #output

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

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