SQL Server Version/Table Size Query
Closed     Case # 10046     Affiliated Job:  BaBaDou DOT Com
Opened:  Thursday, October 14, 2010     Closed:  Thursday, October 14, 2010
Total Hit Count:  22684     Last Hit:  Saturday, June 22, 2024 5:13:45 AM
Unique Hit Count:  3994     Last Unique Hit:  Saturday, June 22, 2024 5:13:45 AM
Case Type(s):  Database
Case Notes(s):  All cases are posted for review purposes only. Any implementations should be performed at your own risk.

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.

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--
    @max INT,
    @min INT,
    @owner NVARCHAR(256),
    @table_name NVARCHAR(256),
    @sql NVARCHAR(4000)
    DECLARE @table TABLE(
    owner_name NVARCHAR(256),
    table_name NVARCHAR(256))
    IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL
    DROP TABLE #results
    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)
    sysobjects so
    INNER JOIN sysusers su ON so.uid = su.uid
    so.xtype = 'U'
    @min = 1,
    @max = (SELECT MAX(ident) FROM @table)
    WHILE @min <= @max
    @owner = owner_name,
    @table_name = table_name
    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
    SELECT * FROM #results
    ^--End Code--^

Profile IMG: Footer Left Profile IMG: Footer Right