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'