Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Working With System Tables Expand / Collapse
Author
Message
Posted Monday, December 19, 2005 5:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 16, 2014 10:56 AM
Points: 27, Visits: 300
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aGrinberg/workingwithsystemtables.asp
Post #245397
Posted Monday, January 16, 2006 12:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 9:02 AM
Points: 125, Visits: 338

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
Posted Thursday, January 19, 2006 10:39 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 12:54 PM
Points: 656, Visits: 262

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
Posted Tuesday, January 24, 2006 1:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Wednesday, January 17, 2007 1:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase

Permissions Expand / Collapse