Wednesday, 21 April 2010

HOWTO: Find out the Sizes of All the User Tables in a Database in SQL

Do you need to know the sizes for all the user tables in your database? I found this script by Mitchel Sellers, an SQL Server MVP,  to be very handy. I've modified it slightly, but you can get the original on his blog.

Thank you Mitchel for allowing me to post this here.

Please excuse the poor formatting, I am working on finding and implementing a decent code formatted for both SQL and C#.


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:            Mitchel Sellers - mitchel.sellers@gmail.com
-- Create date: 2007/06/27
-- Description: Obtains spaced used data for
--                            ALL user tables in the database.
--                            This does not update the space
--                            usage information - implicity
--                            running DBCC UPDATEUSAGE on the
--                            database - as recalculation has
--                            the potential to be VERY costly
--                            if it is run on ALL tables at
--                            once inside a database.
--
-- Modifications:
-- Author:            A. Jackson
-- Date:                2010/03/10
-- Description: Renamed the stored procedure
--                            from
--                            GetAllTableSizes to
--                            dba_GetAllTableSizes to fit my
--                            DBA sp naming convention.
--                        : Changed the select to order the
--                            result by TableName with the
--                            option to order the results by
--                            descending ReservedSizeKB.
--                            In order to do this I had to
--                            strip out the " KB" out of the
--                            size strings and alter the
--                            ReservedSizeKB column definition
--                            after stripping out the " KB".
--                        : Neatened up the SQL.
--                        : Corrected a spelling error in
--                            the comments:
--                            ("reults" -> "results")
-- Author:            A. Jackson
-- Date:                2010/03/24
-- Description: Changed the type of
--                            @OrderByReservedSize from
--                            VARCHAR(5) to BIT.
-- =============================================

CREATE PROCEDURE [dbo].[dba_GetAllTableSizes]
        @OrderByReservedSize BIT = 0
AS  
BEGIN
        --For storing values in the cursor
        DECLARE @TableName VARCHAR(100)
        --Cursor to get the name of all user tables from the sysobjects listing
        DECLARE tableCursor CURSOR FAST_FORWARD READ_ONLY FOR
                SELECT
                        [name]
                FROM
                        dbo.sysobjects
                WHERE
                        OBJECTPROPERTY(id, N'IsUserTable') = 1

        --A procedure level temp table to store the results
        CREATE TABLE #TempTable
                (
                 TableName VARCHAR(100),
                 NumberOfRows VARCHAR(100),
                 ReservedSizeKB VARCHAR(50),
                 DataSizeKB VARCHAR(50),
                 IndexSizeKB VARCHAR(50),
                 UnusedSizeKB VARCHAR(50)
                )

        --Open the cursor
        OPEN tableCursor

        --Get the first table name from the cursor
        FETCH NEXT FROM tableCursor INTO @TableName

        --Loop until the cursor was not able to fetch
        WHILE (@@Fetch_Status >= 0)
        BEGIN
                --Dump the results of the sp_spaceused query to the temp table
                INSERT #TempTable EXEC sp_spaceused @TableName

                --Get the next table name
                FETCH NEXT FROM tableCursor INTO @TableName
        END

        --Get rid of the cursor
        CLOSE tableCursor
        DEALLOCATE tableCursor

        --Strip out the " KB" out of the size strings so that they can be ordered.
        UPDATE
                [#TempTable]
        SET  
                [#TempTable].[ReservedSizeKB] = LEFT([#TempTable].[ReservedSizeKB], LEN([#TempTable].[ReservedSizeKB]) - 3),
                [#TempTable].[DataSizeKB] = LEFT([#TempTable].[DataSizeKB], LEN([#TempTable].[DataSizeKB]) - 3),
                [#TempTable].[IndexSizeKB] = LEFT([#TempTable].[IndexSizeKB], LEN([#TempTable].[IndexSizeKB]) - 3),
                [#TempTable].[UnusedSizeKB] = LEFT([#TempTable].[UnusedSizeKB], LEN([#TempTable].[UnusedSizeKB]) - 3)
        --Alter the temp table so that the results can be ordered.
        ALTER TABLE [#TempTable] ALTER COLUMN [ReservedSizeKB] BIGINT
        
        --Select all records so we can use the results.
        --Optionally ORDER BY ReservedSizeKB
        IF (@OrderByReservedSize = 1)
        BEGIN
                SELECT
                        [#TempTable].[TableName],
                        [#TempTable].[NumberOfRows],
                        [#TempTable].[ReservedSizeKB],
                        [#TempTable].[DataSizeKB],
                        [#TempTable].[IndexSizeKB],
                        [#TempTable].[UnusedSizeKB]
                FROM
                        #TempTable
                ORDER BY
                        [#TempTable].[ReservedSizeKB] DESC
        END
        ELSE
        BEGIN
                SELECT
                        [#TempTable].[TableName],
                        [#TempTable].[NumberOfRows],
                        [#TempTable].[ReservedSizeKB],
                        [#TempTable].[DataSizeKB],
                        [#TempTable].[IndexSizeKB],
                        [#TempTable].[UnusedSizeKB]
                FROM
                        #TempTable
                ORDER BY
                        [#TempTable].[TableName]
        END
        
        --Final cleanup!
        DROP TABLE #TempTable
END
GO

HOWTO: Generate a Random String of Characters in SQL

Here's a stored procedure to generate a string of random characters. This is useful when, for example, one needs to generate a random password. Please excuse the poor formatting, I am working on finding and implementing a decent code formatted for both SQL and C#.

Thanks to Raymond Lewallen over at CodeBetter.com


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:            Raymond Lewallen
--                    http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/17/63281.aspx
-- Create date: 2005/05/16
-- Description: Generate a random string of
--                            given length.
--                        : Everything is self-explanatory.
--                        : Right now max length is set to
--                            100. So anything between 1 and
--                            100 will work for a length.
--                        : If you specify a
--                            @charactersToUse, the bit flags
--                            get ignored.
--                        : All spaces are stripped from the
--                            @charactersToUse.
--                        : Characters can repeat. Will be
--                            handled in a future version.
--
-- Modifications:
-- Author:            A. Jackson
-- Create date: 2010/04/21
-- Description: Renamed the sp to fit into the
--                            naming convention of the
--                            database.
--                        : Formatted the header.
--                        : Reordered the parameters so that
--                            the @toReturn OUT parameter is
--                            first.
--                        : Added @useSpecialCharacters BIT.
--                        : Formatted the code.
--                        : Added default parameter values.
-- =============================================
CREATE PROCEDURE [dbo].[spGenerateRandomString]
        (
            @toReturn VARCHAR(100) OUT,
            @useSpecialCharacters BIT = 1,
            @useNumbers BIT = 1,
            @useLowerCase BIT = 1,
            @useUpperCase BIT = 1,
            @charactersToUse VARCHAR(100) = '',
            @stringLength INT = 100
        )
AS
BEGIN
        IF @stringLength <= 0  
        BEGIN
                RAISERROR ( 'Cannot generate a random string of zero length.', 16, 1 )
        END

        DECLARE @characters VARCHAR(110)
        DECLARE @count INT

        SET @characters = ''

        IF @useSpecialCharacters = 1
        BEGIN
        SET @count = 33
        WHILE @count <= 38
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 40
        WHILE @count <= 47
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 58
        WHILE @count <= 64
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 91
        WHILE @count <= 95
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
                
        SET @count = 123
        WHILE @count <= 126
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
    END
    
        IF @useNumbers = 1  
        BEGIN
                -- load up numbers 0 - 9
                SET @count = 48
                WHILE @count <= 57
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
        END
        
        IF @useLowerCase = 1  
        BEGIN
        -- load up uppercase letters A - Z
        SET @count = 65
        WHILE @count <= 90
        BEGIN
                SET @characters = @characters +
                        CAST(CHAR(@count) AS CHAR(1))
                SET @count = @count + 1
        END
        END

        IF @useUpperCase = 1  
        BEGIN
                -- load up lowercase letters a - z
                SET @count = 97
                WHILE @count <= 122
                BEGIN
                        SET @characters = @characters +
                                CAST(CHAR(@count) AS CHAR(1))
                        SET @count = @count + 1
                END
        END

        SET @count = 0
        SET @toReturn = ''

        -- If you specify a character set to use, the bit flags get ignored.
        IF LEN(@charactersToUse) > 0  
        BEGIN
                WHILE CHARINDEX(@charactersToUse, ' ') > 0
                BEGIN
                        SET @charactersToUse = REPLACE(@charactersToUse, ' ', '')
                END

                IF LEN(@charactersToUse) = 0  
                BEGIN
                        RAISERROR ( 'Cannot use an empty character set.', 16, 1 )
                END

                WHILE @count <= @stringLength
                BEGIN
                        SET @toReturn = @toReturn + SUBSTRING(@charactersToUse,
                                                                                                    CAST(ABS(CHECKSUM(NEWID())) *
                                                                                                    RAND(@count) AS INT) %
                                                                                                    LEN(@charactersToUse) +
                                                                                                    1, 1)
                        SET @count = @count + 1
                END
        END
        ELSE  
        BEGIN
                WHILE @count <= @stringLength
                BEGIN
                        SET @toReturn = @toReturn + SUBSTRING(@characters,
                                                                                                    CAST(ABS(CHECKSUM(NEWID())) *
                                                                                                    RAND(@count) AS INT) %
                                                                                                    LEN(@characters) + 1,
                                                                                                    1)
                        SET @count = @count + 1
                END
        END
END

GO