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:
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;
Posted in SQL Server 2005 | Leave a Comment »
To get the output value of a dynamic SQL query, open a new query window and execute the following
line of code:
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];
Posted in SQL Server 2005 | Leave a Comment »
To select records from a table into XML format, open a new query window and execute the following line of code:
SELECT *
FROM <TableName>
FOR XML AUTO, ROOT;
-- The above select will return rows in the following structure
-- <root>
-- <TableName ColumnName="ColumnValue" />
-- </root>
Posted in SQL Server 2005 | Leave a Comment »
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:
(@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;
Posted in SQL Server 2005 | Leave a Comment »
To find a column name across all tables in a database, open a new query window and execute the following line of code:
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];
Posted in SQL Server 2005 | Leave a Comment »
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:
(@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;
Posted in SQL Server 2005 | Leave a Comment »
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:
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
Posted in SQL Server 2005 | Leave a Comment »
To get a list of all identity columns in a database, open a new query window and execute the following line of code:
SELECT OBJECT_NAME([object_id]) AS [TableName]
,[name] AS [ColumnName]
,[seed_value]
,[last_value]
,[increment_value]
FROM [sys].[identity_columns]
ORDER BY [TableName]
Posted in SQL Server 2005 | Leave a Comment »
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:
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
Posted in SQL Server 2005 | Leave a Comment »
