May 28, 2003 at 11:58 pm
Hi!
Is there a system tables, that can be queried to get the number (and probably the type and participating columns) of indexes in each user table in database?
May 29, 2003 at 5:10 am
This should help.
select
SO.[name] as TableName,
SI.[name] IndexName,
SC.[name] ColName,
ST.[name] TypeVal,
CASE WHEN (SI.status & 16)<>0 THEN 'Yes' ELSE 'No' END as ClusteredIndex
from
sysobjects SO
INNER JOIN
sysindexes SI
INNER JOIN
sysindexkeys SIK
ON
SIK.[id] = SI.[id] AND
SIK.indid = SI.indid
INNER JOIN
syscolumns SC
INNER JOIN
systypes ST
ON
SC.xtype = ST.xtype
ON
SIK.[id] = SC.[id] AND
SIK.colid = SC.colid
ON
SO.[id] = SI.[id]
WHERE
SO.xtype = 'u' AND SI.indid > 0 AND SI.indid < 255 AND (SI.status & 64)=0
ORDER BY
TableName,
IndexName,
SIK.keyno
May 29, 2003 at 6:03 am
This works fine for DBs like Northwind, but does not work for my working DBs (returns nothing) This is the second time I face such situation. This procedure also returned nothing for my working DBs and worked fine for junk DBs:
(Stored procedure does not work with some databases topic)
What DB settings may cause this?
======================================
CREATE PROCEDURE usp_get_record_counts AS
-- Written by: Greg Larsen Date: 2/28/22002
-- All rights reserved Copyright 2002
declare @CMD char(2000)
declare @DB varchar(100)
-- don't need counts to be displayed
set nocount on
-- define cursor to hold list of databases
declare db cursor for
select name from master..sysdatabases
-- Open database cursor
open db
-- get first database
fetch next from db into @db
-- Process until no more databases
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print header
print 'Record Counts for database ' + @DB
-- Build command to get the record counts for current database
set @CMD = 'SELECT ' + char(39) + @DB + char(39) +
'as DB, rows, b.name FROM ' +
@DB + '..sysindexes a , ' + @DB + '..sysobjects b ' +
'WHERE a.id = b.id and type=''u'''+
'AND indid < 2 order by b.name'
-- Process command to return record counts
exec (@CMD)
-- get next database
fetch next from db into @db
end
-- Clean up
close db
deallocate db
======================================
The output looks like:
======================================
Record Counts for database MANAGE
DB,rows,name
MANAGE,8,BackupDatabases
Record Counts for database master
DB,rows,name
master,0,dtproperties
master,38940,locks
master,2,MSreplication_options
master,199626,proc_use
master,199565,proc_use2
master,36,spt_datatype_info
master,10,spt_datatype_info_ext
master,0,spt_fallback_db
master,0,spt_fallback_dev
master,0,spt_fallback_usg
master,1,spt_monitor
master,25,spt_provider_types
master,29,spt_server_info
master,728,spt_values
master,78130,trace1
master,110904,trace2
master,195519,trace3
master,206398,trace4
master,199700,trace5
master,199502,trace6
Record Counts for database mbsII
DB,rows,name
Record Counts for database model
DB,rows,name
=======================================
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply