SQLServerCentral / Discuss Content Posted by Alex Grinberg / Article Discussions / Article Discussions by Author / Working With System Tables / Latest Posts

RE: Working With System Tables

Jeff Scott-375773 — Wed, 17 Jan 2007 13:51:00 GMT

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 poisonALTER PROCEDURE [dbo].[sp_SearchTables] ( @TextPart varchar(1000))AS set nocount onDECLARE @data table ( ind int identity(1,1), query varchar(1000))DECLARE @count intDECLARE @iRow intDECLARE @sql varchar(1000)CREATE table #output ( Context varchar(255) null, ColumnName varchar(255) null, TableName varchar(255) null)INSERT INTO @dataselect '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.idwhere o.type = 'u' and c.id > 500 and t.name in ('varchar', 'char')order by 1SET @count = @@ROWCOUNTSET @iRow = 1WHILE @iRow <= @countBEGIN select @sql = query from @data where ind = @iRow exec(@sql) SET @iRow = @iRow + 1ENDselect * from #outputdrop table #output

RE: Working With System Tables

fai0626 — Tue, 24 Jan 2006 01:57:00 GMT

Hi there,

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

RE: Working With System Tables

SeaQL — Thu, 19 Jan 2006 10:39:00 GMT

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.

RE: Working With System Tables

webtekkie — Mon, 16 Jan 2006 12:53:00 GMT

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

Working With System Tables

Alex Grinberg-230981 — Mon, 19 Dec 2005 17:01:00 GMT

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