Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bmsra79
Starting Member
24 Posts |
 Posted - 2012-10-08 : 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]ASSELECT SERVERPROPERTY('SERVERNAME') AS ServerName, DB_NAME() AS DatabaseName, s.name + '.' + o.name AS TableName, SUM(p.rows) AS RecordCount, COUNT(c.column_id) AS ColumnCountFROM 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_idWHERE (i.index_id < 2) AND (o.type = 'U')GROUP BY s.name, o.nameI 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, ColumnCountPROD1, DB1, TABLE1, "Col1, Col2, Col3", User1, 2012-01-15, 2012-01-30, User2, 30, 3 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Brief Database Summary Information