About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Monday, December 30, 2013

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'






No comments:

Post a Comment