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:  4777     Last Hit:  Monday, September 25, 2017 1:55:03 PM
Unique Hit Count:  1931     Last Unique Hit:  Monday, September 25, 2017 1:55:03 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--^



Profile IMG: Footer Left Profile IMG: Footer Right