String Splitting Function by Delimiter – Into Columns

To create a function that will take a delimited string and return the values as a table  as one row, open a new query window and execute the following line of code:

Code Snippet

-- Function to return string of values as a table with column values
CREATE FUNCTION [dbo].[f_MySplitRoutine2]
               (@StringInput VARCHAR(MAX)
               ,@Delimiter   VARCHAR(1)
               )
RETURNS @OutputTable TABLE ([String] VARCHAR(100), [String2] VARCHAR(100))
AS
BEGIN
    -- Declare variables
    DECLARE @String      VARCHAR(10);
    DECLARE @StringValue VARCHAR(10);

    -- Initialise variables
    SET @String = NULL;

    -- Loop through the string right up to the end
    WHILE LEN(@StringInput) > 0
    BEGIN
        -- Look for the delimiter in the string
        SET @StringValue = LEFT(@StringInput
                          ,ISNULL
                            (NULLIF(CHARINDEX(@Delimiter, @StringInput) – 1, -1)
                            ,LEN(@StringInput)
                            )
                          );
        -- Extract the portion of the string that has been checked
        SET @StringInput = SUBSTRING
                            (@StringInput
                            ,ISNULL
                              (NULLIF(CHARINDEX(@Delimiter, @StringInput), 0)
                              ,LEN(@StringInput)
                              ) + 1, LEN(@StringInput)
                            );
        -- Store values until the pair is ready for insert
        IF @String IS NULL
            BEGIN
                SET @String      = @StringValue;
                SET @StringValue = NULL;
            END;
        -- Insert the string details into table output
        IF @String IS NOT NULL AND @StringValue IS NOT NULL
            BEGIN
                -- Insert the record into the output table
                INSERT INTO @OutputTable ([String], [String2])
                VALUES                   (@String,  @StringValue);
                SET @String      = NULL;
                SET @StringValue = NULL;
            END;
    END;

    RETURN;

END;

Get Output From Dynamic SQL Query

To get the output value of a dynamic SQL query, open a new query window and execute the following
line of code:

Code Snippet

-- Declare variables
DECLARE @lvSQL         NVARCHAR(2000) -- This MUST be NVARCHAR!!!
DECLARE @liCount       INT;
DECLARE @liCountOutput INT;

-- Create temp table
CREATE TABLE [MyTable] ([col1] VARCHAR(10));

-- Insert records into temp table
INSERT INTO [Mytable] ([col1]) VALUES ('1');
INSERT INTO [Mytable] ([col1]) VALUES ('2');
INSERT INTO [Mytable] ([col1]) VALUES ('3');

-- Build the dynamic SQL
SET @lvSQL = 'SELECT @liCount = COUNT(*) FROM [Mytable]';

-- Call system stored procedure to execute the dynamic SQL
EXEC SP_EXECUTESQL @lvSQL, N'@liCount INT OUTPUT', @liCount OUTPUT;

-- Assign the output parameter
SET @liCountOutput = @liCount;

-- Select the output value
SELECT @liCountOutput;

-- Drop temp table
DROP Table [MyTable];

Select Records Into XML Structure

To select records from a table into XML format, open a new query window and execute the following line of code:

Code Snippet

-- Get a records from table into XML format
SELECT  *
FROM    <TableName>
FOR XML AUTO, ROOT;

-- The above select will return rows in the following structure
-- <root>
--     <TableName ColumnName="ColumnValue" />
-- </root>

Search Text In All Tables

To search for text in all database columns that hold CHAR, VARCHAR, NCHAR and NVARCHAR data types for all tables, open a new query window and execute the following line of code:

Code Snippet

CREATE PROCEDURE p_SearchForText
                (@iSearchString VARCHAR(100))
AS
BEGIN

    SET NOCOUNT ON;

    -- Declare variables
    DECLARE @lvTableName     VARCHAR(100);
    DECLARE @lvColumnName    VARCHAR(100);
    DECLARE @lvSQL           VARCHAR(2000);
    DECLARE @lvSearchString2 VARCHAR(2000);

    -- Declare temp table
    DECLARE @MyTable TABLE ([ColumnName]  VARCHAR(200)
                           ,[ColumnValue] VARCHAR(2000)
                           );

    -- Initialise variables
    SET @lvTableName     = '';
    SET @lvColumnName    = '';
    SET @lvSQL           = '';
    SET @lvSearchString2 = QUOTENAME('%' + @iSearchString + '%', '''');

    -- Declare cursor to get list of tables in the system
    DECLARE curTableList CURSOR FOR
    SELECT  QUOTENAME([table_schema]) + '.' + QUOTENAME([table_name])
    FROM    [information_schema].[tables]
    WHERE   [table_type] = 'BASE TABLE'
    AND     [table_name] LIKE '%<TableName>%';

    -- Open cursor to get list of tables to process
    OPEN  curTableList
    FETCH NEXT FROM curTableList
    INTO  @lvTableName;

    -- Loop until all tables are processed
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Declare cursor to get list of columns for the table
        DECLARE curTableColumnList CURSOR FOR
        SELECT  QUOTENAME([column_name])
        FROM    [information_schema].[columns]
        WHERE   [table_schema]  = PARSENAME(@lvTableName, 2)
        AND     [table_name]    = PARSENAME(@lvTableName, 1)
        AND     [data_type]    IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR');

        -- Open cursor to get list of tables to process
        OPEN  curTableColumnList
        FETCH NEXT FROM curTableColumnList
        INTO  @lvColumnName;

        -- Loop until all tables are processed
        WHILE @@FETCH_STATUS = 0
        BEGIN

            -- Initiliase variables
            SET @lvSQL = '';

            -- Build the SQL statement to insert the table column
            SET @lvSQL = 'SELECT ''' + @lvTableName + '.' + @lvColumnName + ''',
                         ' + @lvColumnName + ' ' +
                         'FROM ' + @lvTableName + ' (NOLOCK) ' + ' ' +
                         'WHERE ' + @lvColumnName + ' LIKE ' + @lvSearchString2;

            -- Insert record into temp table
            INSERT INTO @Mytable
            EXEC (@lvSQL);

            -- Get the next column from the list
            FETCH NEXT FROM curTableColumnList
            INTO  @lvColumnName;

        END;

        -- Close and deallocate the cursor for column list
        CLOSE      curTableColumnList;
        DEALLOCATE curTableColumnList;

        -- Get the next table from the list
        FETCH NEXT FROM curTableList
        INTO  @lvTableName;

    END;

    -- Close and deallocate the cursor for table list
    CLOSE      curTableList;
    DEALLOCATE curTableList;

    -- Select the results
    SELECT [ColumnName]
          ,[ColumnValue]
    FROM   @MyTable;

END;

Find Same Columns Across All Tables

To find a column name across all tables in a database, open a new query window and execute the following line of code:

Code Snippet

-- Get a particular column name from all tables
SELECT   [SysObjects].[name]   AS [TableName]
        ,[SysColumns].[name]   AS [ColumnName]
        ,[SysTypes].[name]     AS [ColumnDataType]
        ,[SysColumns].[length] AS [ColumnLength]
FROM     [SysObjects]
         INNER JOIN [SysColumns] ON [SysObjects].[id]  = [SysColumns].[id]
         INNER JOIN [SysTypes]   ON [SysTypes].[xtype] = [SysColumns].[xtype]
WHERE    [SysObjects].[type] = 'U'
AND      [SysColumns].[name] LIKE '%<ColumnName>%'
ORDER BY [SysObjects].[name] 
        ,[SysColumns].[name];

Search & Replace Database Column Text

To search an replace text in database columns that hold CHAR, VARCHAR, NCHAR and NVARCHAR data types, open a new query window and execute the following line of code:

Code Snippet

CREATE PROCEDURE p_SearchAndReplace
                (@iSearchString VARCHAR(100)
                ,@iReplaceString VARCHAR(100)
                )
AS
BEGIN

    SET NOCOUNT ON;

    -- Declare variables
    DECLARE @lvTableName     VARCHAR(100);
    DECLARE @lvColumnName    VARCHAR(100);
    DECLARE @lvSQL           VARCHAR(2000);
    DECLARE @lvSearchString2 VARCHAR(2000);
    DECLARE @liCounter       INT;
    DECLARE @lvColumnList    VARCHAR(2000);

    -- Initialise variables
    SET @lvTableName     = '';
    SET @lvColumnName    = '';
    SET @lvSQL           = '';
    SET @lvSearchString2 = QUOTENAME('%' + @iSearchString + '%', '''');
    SET @liCounter       = 0;
    SET @lvColumnList    = '';

    -- Declare cursor to get list of tables in the system
    DECLARE curTableList CURSOR FOR
    SELECT  QUOTENAME([table_schema]) + '.' + QUOTENAME([table_name])
    FROM    [information_schema].[tables]
    WHERE   [table_type] = 'BASE TABLE'
    AND     [table_name] LIKE '%<TableName>%';

    -- Open cursor to get list of tables to process
    OPEN  curTableList
    FETCH NEXT FROM curTableList
    INTO  @lvTableName;

    -- Loop until all tables are processed
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Initiliase variables
        SET @lvColumnList = '';

        -- Declare cursor to get list of columns for the table
        DECLARE curTableColumnList CURSOR FOR
        SELECT  QUOTENAME([column_name])
        FROM    [information_schema].[columns]
        WHERE   [table_schema] = PARSENAME(@lvTableName, 2)
        AND     [table_name]   = PARSENAME(@lvTableName, 1)
        AND     [data_type]    IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR');

        -- Open cursor to get list of tables to process
        OPEN  curTableColumnList
        FETCH NEXT FROM curTableColumnList
        INTO  @lvColumnName;

        -- Loop until all tables are processed
        WHILE @@FETCH_STATUS = 0
        BEGIN

            -- Initiliase variables
            SET @lvSQL = '';

            -- Build the SQL statement to update the table column
            SET @lvSQL = 'UPDATE ' + @lvTableName + ' ' +
                         'SET '    + @lvColumnName + ' = REPLACE(' +
                                     @lvColumnName + ', ' +
                                     QUOTENAME(@iReplaceString, '''') + ') ' +
                         'WHERE '  + @lvColumnName + ' LIKE ' +
                                     @lvSearchString2;

            -- Run the SQL statement
            EXEC (@lvSQL);

            -- Get the number of rows that were affected
            SET @liCounter = @@ROWCOUNT;

            -- If a record was update then get the column that was changed
            IF @liCounter > 0
                BEGIN
                    IF @lvColumnList = ''
                        SET @lvColumnList = @lvColumnName;
                    ELSE
                        SET @lvColumnList = @lvColumnList + ',' + @lvColumnName;
                END;

            -- Get the next column from the list
            FETCH NEXT FROM curTableColumnList
            INTO  @lvColumnName;

        END;

        -- Show counter details
        SELECT 'Replaced ' + CAST(@liCounter AS VARCHAR) +
               ' occurence(s) for table ' + @lvTableName + ' for columns ' +                @lvColumnList AS 'Outcome';

        -- Close and deallocate the cursor for column list
        CLOSE      curTableColumnList;
        DEALLOCATE curTableColumnList;

        -- Get the next table from the list
        FETCH NEXT FROM curTableList
        INTO  @lvTableName;

    END;

    -- Close and deallocate the cursor for table list
    CLOSE      curTableList;
    DEALLOCATE curTableList;

END;

String Splitting Function by Delimiter

To create a function that will take a delimited string and return the values as a table, open a new query window and execute the following line of code:

Code Snippet

-- Function to return string of values as a table with single values
CREATE FUNCTION [dbo].[f_MySplitRoutine]
               (@StringInput VARCHAR(MAX)
               ,@Delimiter VARCHAR(1)
               )
RETURNS @OutputTable TABLE ([String] VARCHAR(100))
AS
BEGIN

    -- Declare variables
    DECLARE @String VARCHAR(10)

    -- Loop through the string right up to the end
    WHILE LEN(@StringInput) > 0
    BEGIN
        -- Look for the delimiter in the string
        SET @String = LEFT(@StringInput
                          ,ISNULL
                            (NULLIF(CHARINDEX(@Delimiter, @StringInput) – 1, -1)
                            ,LEN(@StringInput)
                            )
                          )
        -- Extract the portion of the string that has been checked
        SET @StringInput = SUBSTRING
                            (@StringInput
                            ,ISNULL
                              (NULLIF(CHARINDEX(@Delimiter, @StringInput), 0)
                              ,LEN(@StringInput)
                              ) + 1, LEN(@StringInput)
                            )
        -- Insert the record into the output table
        INSERT INTO @OutputTable ([String]) VALUES (@String)
    END

    RETURN

END

Find All Identity Columns In Database

To get a list of all identity columns in a database, open a new query window and execute the following line of code:

Code Snippet

-- Get all identity columns from the database
SELECT   OBJECT_NAME([object_id]) AS [TableName]
        ,[name]                   AS [ColumnName]
        ,[seed_value]
        ,[last_value]
        ,[increment_value]
FROM     [sys].[identity_columns]
ORDER BY [TableName]

Tree Walking With Recursive Function

To perform a tree walking routine to find all the top level menu options for a particular sub menu option, open a new query window and execute the following line of code:

Code Snippet

-- Create a new test table
CREATE TABLE [MyTest] ([Test_PK]     INT IDENTITY
                      ,[Test_Menu]   VARCHAR(50)
                      ,[Test_Parent] INT)

-- Insert test data into the table
INSERT INTO [MyTest] VALUES ('Level A', NULL)
INSERT INTO [MyTest] VALUES ('Level B', NULL)
INSERT INTO [MyTest] VALUES ('Sub Level A', 1)
INSERT INTO [MyTest] VALUES ('Sub Level B', 2)
INSERT INTO [MyTest] VALUES ('Sub Sub Level A', 3)
INSERT INTO [MyTest] VALUES ('Sub Sub Level A', 3)
INSERT INTO [MyTest] VALUES ('Sub Sub Level B', 4)

-- Declare variables
DECLARE @Test_PK INT

-- Initiliase variables
SET @Test_PK = 0

-- Get the key for the level of the tree that you are after
SELECT @Test_PK = [Test_PK]
FROM   [MyTest]
WHERE  [MyTest].[Test_Menu] = 'Sub Sub Level B'

-- Start tree walking routine here
BEGIN
    WITH MenuLevels([Test_PK], [Test_Menu], [Test_Parent])
    AS
    (
     -- Anchor member
     SELECT [Test_PK]
           ,[Test_Menu]
           ,[Test_Parent]
     FROM   [MyTest]
     WHERE  [Test_PK] = @Test_PK
     UNION ALL
     -- Recursive member
     SELECT [MyTest].[Test_PK]
           ,[MyTest].[Test_Menu]
           ,[MyTest].[Test_Parent]
     FROM   [MyTest]
            JOIN [MenuLevels] ON [MenuLevels].[Test_Parent] = [MyTest].[Test_PK]
    )
    -- Outer query
    SELECT   *
    FROM     [MenuLevels]
    ORDER BY [Test_PK]
END

-- Drop the table
DROP TABLE MyTest

Return Rows Into Single Concatenated List

To select a result set and return the results in a single concatenated string, open a new query window and execute the following line of code:

Code Snippet

-- Create a new test table
CREATE TABLE [MyTest] ([TestValue] VARCHAR(10))

-- Insert test data into the table
INSERT INTO [MyTest] VALUES ('A Test')
INSERT INTO [MyTest] VALUES ('B Test')
INSERT INTO [MyTest] VALUES ('C Test')
INSERT INTO [MyTest] VALUES ('D Test')
INSERT INTO [MyTest] VALUES ('E Test')

-- Declare variables
DECLARE @Result    VARCHAR(MAX)
DECLARE @Space     VARCHAR(1)
DECLARE @Delimiter VARCHAR(1)

-- Initialise varibales
SET @Result    = ''
SET @Space     = '|'
SET @Delimiter = ','

-- Use XML structure to output values into a single string
SELECT @Result = REPLACE
                 (
                  (SELECT   REPLACE([TestValue], ' ', @Space) AS [data()]
                   FROM     [MyTest]
                   ORDER BY [TestValue]
                   FOR XML PATH ('')
                  ), ' ', @Delimiter
                 )

-- Replace the '|' characters with spaces again
SELECT @Result = REPLACE(@Result, @Space, ' ')

-- Output the result
PRINT ''
PRINT @Result

-- Drop the table
DROP TABLE MyTest