Find Database Object information

Here is a script to find All Tables in a Database. It will also find No of Records, Indexes, No of Indexes, Space occupied by a Table.

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