vwRowCount

USE Admin

GO

IF object_id('dbo.vwRowCount') Is Not Null

      DROP VIEW dbo.vwRowCount

go

Create View dbo.vwRowCount

AS

/*    DATE        AUTHOR            REMARKS

      1/16/12           PPaiva            Initial creation.

      SELECT *

      FROM vwRowCount

      ORDER BY Rows desc

      SELECT *

      FROM vwRowCount

      ORDER BY TableName

      DEPENDENCY

            dbo.udfGetColumnList()

*/

WITH cteCols

AS    (     SELECT object_id, Count(*) NumCols

            FROM sys.columns

            GROUP BY Object_ID

            )

SELECT  Convert(varchar, ServerProperty('ServerName')) as Server,

            DB_NAME() AS DB,

            SCHEMA_NAME(t.schema_id) AS SchemaName,

            t.name AS TableName,

                  dbo.udfFormatInteger(i.Rows) RowsFMT,

                  col.NumCols,

                  dbo.udfGetColumnList(t.name, 0) ColList,

                  t.create_date CreateDate,

                  t.modify_date ModifyDate,

                  'SELECT * FROM ' + DB_NAME() + '.[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' SqlSELECT,

                  dbo.udfGetColumnList(t.name, 1) ColListBRACKETS,

                  'SELECT TOP 500 * FROM ' + DB_Name() + '.[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' SqlSELECT500,

            '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS FullTableName,

            i.Rows,

            t.object_id

FROM sys.tables t

JOIN sys.sysindexes i

      ON  t.object_id = i.id

      AND i.indid < 2         -- 0 = heap, 1 = has clustered index, >1 is non-clustered index

JOIN cteCols col

      ON col.object_id = t.object_id 

  AND t.name <> 'sysdiagrams'