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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy