|
bmsra79
Starting Member
24 Posts |
 Posted - 10/08/2012 : 07:58:58
|
I have a View that gives me Brief Database Summary. I want it expanded to include few other fields.
CREATE VIEW [RowsColsCounts]
AS
SELECT SERVERPROPERTY('SERVERNAME') AS ServerName, DB_NAME() AS DatabaseName, s.name + '.' + o.name AS TableName, SUM(p.rows) AS RecordCount,
COUNT(c.column_id) AS ColumnCount
FROM sys.indexes AS i INNER JOIN
sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN
sys.objects AS o ON o.object_id = i.object_id INNER JOIN
sys.columns AS c ON o.object_id = c.object_id INNER JOIN
sys.schemas AS s ON o.schema_id = s.schema_id
WHERE (i.index_id < 2) AND (o.type = 'U')
GROUP BY s.name, o.name
I want to add 5 additional columns added into this View Definition
- ColumnNames (A Comma separated list of all columns available in that table)
- CreatedBy (Table Created by)
- CreatedOn (Table Creation Date)
- LastModifiedOn (Table Last Modification Date)
- LastModifiedBy (Table Last Modified by)
Sample Output Reqquired:
ServerName, DatabaseName, TableName, ColumnNames, CreatedBy, CreatedOn, LastModifiedOn, LastModifiedBy, RecordCount, ColumnCount
PROD1, DB1, TABLE1, "Col1, Col2, Col3", User1, 2012-01-15, 2012-01-30, User2, 30, 3 |
|
Brief Database Summary Information
