|
Closed
|
Case #
|
10046
|
|
Affiliated Job:
|
BaBaDou DOT Com
|
|
|
Opened:
|
Thursday, October 14, 2010
|
|
Closed:
|
Thursday, October 14, 2010
|
|
Total Hit Count:
|
23509
|
|
Last Hit:
|
Saturday, December 21, 2024 2:50:45 PM
|
|
Unique Hit Count:
|
4345
|
|
Last Unique Hit:
|
Saturday, December 21, 2024 2:50:45 PM
|
|
Case Type(s):
|
Database
|
|
|
Case Notes(s):
|
All cases are posted for review purposes only. Any implementations should be performed at your own risk.
|
|
|
|
Project:
|
|
Two useful queries for SQL server, the first is to determine the server version, something not apparent in SQL 2005 & 2008. The second profiles a database containers table sizes which I have found to be very useful. I found both of these online.
|
|
|
Outcome:
|
Microsoft SQL Product Version:
| | --Start Code-- | | | SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') | | | ^--End Code--^ |
All Table Size within a Database Container:
Source: http://www.dbforums.com/microsoft-sql-server/1003535-query-showing-tablesize-mb.html
| | --Start Code-- | | | DECLARE | | | @max INT, | | | @min INT, | | | @owner NVARCHAR(256), | | | @table_name NVARCHAR(256), | | | @sql NVARCHAR(4000) | | | | | | DECLARE @table TABLE( | | | ident INT IDENTITY(1,1) PRIMARY KEY, | | | owner_name NVARCHAR(256), | | | table_name NVARCHAR(256)) | | | | | | IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL | | | BEGIN | | | DROP TABLE #results | | | END | | | | | | CREATE TABLE #results( | | | ident INT IDENTITY(1,1) PRIMARY KEY, --Will be used to update the owner. | | | table_name NVARCHAR(256), | | | owner_name NVARCHAR(256), | | | table_rows INT, | | | reserved_space NVARCHAR(55), | | | data_space NVARCHAR(55), | | | index_space NVARCHAR(55), | | | unused_space NVARCHAR(55)) | | | | | | INSERT @table(owner_name, table_name) | | | SELECT | | | su.name, | | | so.name | | | FROM | | | sysobjects so | | | INNER JOIN sysusers su ON so.uid = su.uid | | | WHERE | | | so.xtype = 'U' | | | | | | SELECT | | | @min = 1, | | | @max = (SELECT MAX(ident) FROM @table) | | | | | | WHILE @min <= @max | | | BEGIN | | | | | | SELECT | | | @owner = owner_name, | | | @table_name = table_name | | | FROM | | | @table | | | WHERE | | | ident = @min | | | | | | SELECT @sql = 'EXEC sp_spaceused ''[' + @owner + '].[' + @table_name + ']''' | | | | | | INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space) | | | EXEC (@sql) | | | | | | UPDATE #results | | | SET owner_name = @owner | | | WHERE ident = (SELECT MAX(ident) FROM #results) | | | | | | SELECT @min = @min + 1 | | | END | | | | | | SELECT * FROM #results | | | ^--End Code--^ |
|
|
|
|
|
|
|
|
