Working With System Tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Alex Grinberg » Working With System Tables

Working With System Tables

Rate Topic

Display Mode

Topic Options

Author

Message

Alex Grinberg-230981

Posted Monday, December 19, 2005 5:01 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 22, Visits: 240

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

Post #245397

webtekkie

Posted Monday, January 16, 2006 12:53 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, January 19, 2013 6:20 PM
Points: 120, Visits: 299

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

Post #251066

SeaQL

Posted Thursday, January 19, 2006 10:39 AM

SSChasing Mays

Group: General Forum Members
Last Login: Monday, April 29, 2013 6:06 PM
Points: 619, Visits: 229

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.

Post #252102

fai0626

Posted Tuesday, January 24, 2006 1:57 AM

Old Hand

Group: General Forum Members
Last Login: Monday, April 19, 2010 8:20 PM
Points: 380, Visits: 109

Hi there,

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

Post #253000

Jeff Scott-375773

Posted Wednesday, January 17, 2007 1:51 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 13, 2011 10:48 AM
Points: 30, Visits: 58

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

Post #337605

« Prev Topic | Next Topic »

Permissions