December 19, 2005 at 5:01 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aGrinberg/workingwithsystemtables.asp
January 16, 2006 at 12:53 pm
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
January 19, 2006 at 10:39 am
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.
January 24, 2006 at 1:57 am
Hi there,
After you run the sp_spaceused stored procedure, it will show the correct rowcount.
January 17, 2007 at 1:51 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply