SQL Script to find find database object information

Leave a comment (0) Go to comments

At time we might want to track database objects like

  1. what all tables exists on database
  2. Number of records in those tables
  3. Does all tables have indexes or not
  4. How many indexes are built on a particular table
  5. How much space is being occupied by a particular table
  6. What is the total index size of a particular table

If you are looking for answers to all these question, the following script is for you..

Following script will Returns name, type, rows, number of indexes and other key information about the database SQL2000 and later , check output as image

/* Returns name, type, rows, number of indexes and other key information about the database
SQL2000 and later */


SET NOCOUNT ON

CREATE TABLE #TableInfo (Name sysname NULL, 
                         Rows int,
                         Reserved varchar(256) NULL,
                         Data varchar(256) NULL,
                         Index_Size varchar(256) NULL,
                         Unused varchar(256) NULL)
CREATE TABLE #DBTables (Instance sysname NULL,
                        DBName sysname NULL,
                        TableName sysname NULL, 
                        TableType char(2),
                        TableRows int NULL,
                        IndexCount int NULL,
                        ReservedKB int NULL,
                        DataSizeKB int NULL,
                        IndexSizeKB int NULL,
                        UnusedKB int NULL)

DECLARE @DatabaseName varchar(64)
DECLARE @TableName varchar(256)
DECLARE @xtype char(2)
DECLARE @TableRows int
DECLARE @IndexCount int
DECLARE @ReservedKB int 
DECLARE @DataSizeKB int 
DECLARE @IndexSizeKB int
DECLARE @UnusedKB int 

DECLARE cs CURSOR FOR 
  SELECT  
    su.name + '.[' + so.name + ']', 
    so.xtype
  FROM sysobjects so INNER JOIN sysusers su ON (so.uid = su.uid)
  WHERE so.xtype in ('U', 'S') 

SELECT @DatabaseName = DB_NAME(dbid)
FROM master..sysprocesses
WHERE spid=@@SPID


OPEN cs
FETCH NEXT FROM cs INTO @TableName, @xtype
WHILE (@@FETCH_STATUS = 0)
BEGIN
  TRUNCATE TABLE #TableInfo

  IF @xtype = 'U'
    INSERT INTO #TableInfo exec sp_spaceused @TableName , @updateusage = 'TRUE'
  ELSE
    INSERT INTO #TableInfo exec sp_spaceused @TableName 

  SELECT 
    @TableRows = Rows,
    @ReservedKB = CAST(SUBSTRING(Reserved, 1, CHARINDEX('KB', Reserved, 1)-1) AS int),
    @DataSizeKB = CAST(SUBSTRING(Data, 1, CHARINDEX('KB', Data, 1)-1) AS int),
    @IndexSizeKB = CAST(SUBSTRING(Index_Size, 1, CHARINDEX('KB', Index_Size, 1)-1) AS int),
    @UnusedKB = CAST(SUBSTRING(Unused, 1, CHARINDEX('KB', Unused, 1)-1) AS int)
  FROM #TableInfo

  SELECT @IndexCount = COUNT(*) 
  FROM sysindexes 
  WHERE 
    id=OBJECT_ID(@TableName) AND
    name NOT LIKE  '_WA_Sys%' AND
    indid > 0

  INSERT INTO #DBTables 
  VALUES (@@SERVERNAME,
          @DatabaseName, 
          @TableName,
          @xtype,
          @TableRows,
          @IndexCount,
          @ReservedKB,
          @DataSizeKB,
          @IndexSizeKB,
          @UnusedKB)

  FETCH NEXT FROM cs INTO @TableName, @xtype
END
CLOSE cs
DEALLOCATE cs

SELECT 
  Instance,
  DBName,
  TableName,
  TableType,
  TableRows,
  IndexCount,
  ReservedKB,
  DataSizeKB,
  IndexSizeKB,
  UnusedKB
FROM #DBTables

DROP TABLE #DBTables
DROP TABLE #TableInfo


OUTPUT

SQL Script to find find database object information What is the total index size of a particular table what all tables exists on database sql server Does all tables have indexes or not Number of records in a table How much space is being occupied by a particular table How many indexes are built on a particular table

EOF - SQL Script to find find database object information, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.