Tag Archives: SQL

How to generate a random password with T-SQL

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , , , ,

The following script will generate a random 10 character password that meets the complexity requirement for Microsoft Windows. To generate a password just run the script in a new SQL Server Management Studio window. The logic can also be easily turned into a function.

The option of symbol characters is limited to what’s shown below as dealing with quotes and obscure characters in a password is often more trouble than it is worth. The password generated however should still be very secure as it will be 10 characters long with a guaranteed number, lowercase letter, uppercase letter and a symbol.

!
#
$
%
&
(
)
*
+

/*Declare Variables*/
DECLARE @i INT;
DECLARE @Pw VARCHAR(MAX);
DECLARE @Numbers TABLE (Characters CHAR(1));
DECLARE @LowerCase TABLE (Characters CHAR(1));
DECLARE @UpperCase TABLE (Characters CHAR(1));
DECLARE @Symbols TABLE (Characters CHAR(1));
DECLARE @BaseCharacters TABLE (Characters CHAR(1));
DECLARE @GuaranteedCharacters TABLE (Characters CHAR(1));
DECLARE @PwCharacters TABLE (Characters CHAR(1));

/*Generate Numbers*/
SET @i = 0;

WHILE @i <= 9
BEGIN
	INSERT INTO @Numbers
	SELECT @i

	SET @i = @i + 1
END;

/*Generate Lowercase Letters*/
SET @i = 97;

WHILE @i <= 122
BEGIN
	INSERT INTO @LowerCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Uppercase Letters*/
SET @i = 65;

WHILE @i <= 90
BEGIN
	INSERT INTO @UpperCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Symbols*/
SET @i = 33;

WHILE @i <= 43
BEGIN
	IF (
			@i = 34
			OR @i = 39
			)
	BEGIN
		SET @i = @i + 1
	END

	INSERT INTO @Symbols
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*
Randomly Select A Number, Lowercase Letter,
Uppercase Letter And A Symbol So Four Character Types
Are Guaranteed To Be Present Somewhere In The Password
*/
INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Numbers
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @LowerCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @UpperCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Symbols
ORDER BY NEWID();

/*
Randomly Select Another 6 Characters
*/
INSERT INTO @BaseCharacters
SELECT TOP 6 Characters
FROM (
	SELECT Characters
	FROM @Numbers
	
	UNION ALL
	
	SELECT Characters
	FROM @LowerCase
	
	UNION ALL
	
	SELECT Characters
	FROM @UpperCase
	
	UNION ALL
	
	SELECT Characters
	FROM @Symbols
	) AS Characters
ORDER BY NEWID()

/*Generate A 10 Character Password*/
INSERT INTO @PwCharacters (Characters)
SELECT Characters
FROM (
	SELECT Characters
	FROM @BaseCharacters
	
	UNION ALL
	
	SELECT Characters
	FROM @GuaranteedCharacters
	) AS Characters
ORDER BY NEWID()

/*Save The Password To A String*/
SELECT @Pw = COALESCE(@Pw + Characters, Characters)
FROM @PwCharacters

SELECT @Pw AS PW;

 

If you found this post helpful please like, comment and share.

How to search for SQL Server objects that exist anywhere across an instance using T-SQL

Database Administrator, dba, How To, sql, SQL Server, T-SQL, tsql, , , , , , , , , , , , , , , , ,

You’re probably never going to be familiar with every database object, i.e. Table, View, Stored Procedure and Function, that exists in a large production database, even if you were the one who designed it. So everyone who maintains an environment be it a call center back end or sales system back end or CRM back end etc. etc. needs to be able to locate objects quickly.

Redgate offer a fantastic free tool to do this within SQL Server Management Studio through a GUI called SQL Search.

As great as this is though sometimes you might want to search through object definitions programmatically.

To clarify I’m defining object definition as being column names of a table or view or the command that makes up a stored procedure or function.

Below is the Store Procedure I’ve written to do this called SearchObjectDefinition. To work this Stored Procedure also requires the User Defined Function (UDF) called Split which I used in the tutorial “How to pass a multi-value parameter to a stored procedure from a SSRS Report“.

Below are a few use cases for SearchObjectDefinition:

--List All Instance Tables, Stored Procedures, Views and Functions
EXEC dbo.SearchObjectDefinition

--List All Stored Procedures, and Functions in the Databases 
--TestDatabaseOne and TestDatabaseTwo
EXEC dbo.SearchObjectDefinition @ObjectType = 'Sp, Fn'
	,@DatabaseName = 'TestDatabaseOne, TestDatabaseTwo'

--List All Instance Tables, Stored Procedures, Views and Functions 
--where Object Definition contains the word Insert
EXEC dbo.SearchObjectDefinition @strFind = 'insert'

--List All Instance Tables where Object Name is Customers and 
--Column name contains the word Phone
EXEC dbo.SearchObjectDefinition @ObjectType = 'tb'
	,@ObjectName = 'Customers'
	,@strFind = 'Phone'

As always be sure to deploy the following Function and Store Procedure in a utility database not the master database as this is bad practice.

Split Function:

--USE [DatabaseName];
--GO

IF OBJECT_ID('[Split]') IS NULL
	EXEC ('CREATE FUNCTION dbo.[Split](@i INT) RETURNS @RtnValue TABLE (j INT) AS BEGIN INSERT INTO @RtnValue (j) SELECT 1 RETURN END');
GO

ALTER FUNCTION [dbo].[Split] (
	@List NVARCHAR(2000)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE (
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	)
AS
BEGIN
	WHILE (Charindex(@SplitOn, @List) > 0)
	BEGIN
		INSERT INTO @RtnValue (value)
		SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))

		SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))
	END

	INSERT INTO @RtnValue (Value)
	SELECT Value = ltrim(rtrim(@List))

	RETURN
END

SearchObjectDefinition Stored Procedure:

--USE [DatabaseName];
--GO

IF OBJECT_ID('[SearchObjectDefinition]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[SearchObjectDefinition] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[SearchObjectDefinition] (
	@ObjectType AS VARCHAR(20) = NULL
	,@ObjectName AS SYSNAME = NULL
	,@DatabaseName AS SYSNAME = NULL
	,@strFind AS VARCHAR(MAX) = NULL
	)
AS
BEGIN
	SET NOCOUNT ON;
	SET @strFind = ISNULL(@strFind, '')
	SET @ObjectName = ISNULL(@ObjectName, '')

	IF OBJECT_ID('tempdb..#Result') IS NOT NULL
		DROP TABLE #Result;

	DECLARE @DatabaseTable TABLE (DbName SYSNAME)
	DECLARE @DbName AS SYSNAME
	DECLARE @Sql AS VARCHAR(MAX)

	CREATE TABLE #Result (
		DbName SYSNAME NULL
		,ObjectType VARCHAR(2)
		,ObjectName SYSNAME
		,ObjectDefinition VARCHAR(MAX)
		)

	IF @DatabaseName IS NOT NULL
	BEGIN
		INSERT INTO @DatabaseTable (DbName)
		SELECT Value
		FROM dbo.Split(@DatabaseName, ',')
	END

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @DatabaseTable (DbName)
		SELECT NAME
		FROM master.dbo.sysdatabases
		WHERE NAME NOT IN (
				'tempdb'
				,'master'
				,'msdb'
				,'model'
				)
		ORDER BY NAME ASC
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL PROCEDURES  
	IF @ObjectType LIKE '%Sp%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
			USE ' + QUOTENAME(@DbName) + ';
			
			INSERT INTO #Result (
				DbName
				,ObjectType
				,ObjectName
				,ObjectDefinition
				)
			SELECT ''' + @DbName + ''' AS DbName
				,''Sp'' AS ObjectType
				,OBJECT_NAME(OBJECT_ID) AS ObjectName
				,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
			FROM ' + QUOTENAME(@DbName) + '.sys.procedures
			WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
			AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL VIEWS   
	IF @ObjectType LIKE '%Vw%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
		
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Vw'' AS ObjectType
			,OBJECT_NAME(OBJECT_ID) AS ObjectName
			,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.sys.VIEWS
		WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
		AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL FUNCTION 
	IF @ObjectType LIKE '%Fn%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
		
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Fn'' AS ObjectType
			,ROUTINE_NAME AS ObjectName
			,ROUTINE_DEFINITION AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_DEFINITION LIKE ''%'' + ''' + @strFind + ''' + ''%''
			AND ROUTINE_NAME LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			AND ROUTINE_TYPE = ''FUNCTION''
		ORDER BY ROUTINE_NAME
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL TABLES OF DATABASE.  
	IF @ObjectType LIKE '%Tb%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
			
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Tb'' AS ObjectType
			,t.NAME AS ObjectName
			,c.NAME AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
		INNER JOIN ' + QUOTENAME(@DbName) + '.sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
		WHERE c.NAME LIKE ''%'' + ''' + @strFind + ''' + ''%''
		AND t.Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
		ORDER BY [ObjectDefinition] ASC
			'

			EXEC (@Sql)
		END
	END

	SELECT DbName
		,ObjectType
		,ObjectName
		,ObjectDefinition
	FROM #Result
	ORDER BY DbName ASC
		,ObjectType ASC
		,ObjectName ASC

	DROP TABLE #Result
END

 

How to run all enabled SQL Server Jobs with T-Sql

Database Administrator, dba, Development, How To, sql, SQL Server, T-SQL, tsql, , , , , , , , , , ,

I’d really recommend not running the output of this script on a live environment! This is just a little something I whipped up to test changes on a development environment.

Running the script will output the T-Sql required to run all jobs on the SQL Server instance. Just copy the text and paste into a new SSMS window and execute. You could change the PRINT to EXEC but I wouldn’t recommend it.

SET NOCOUNT ON

DECLARE @Job TABLE (JobName SYSNAME)
DECLARE @JobName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @JobName = ''

INSERT INTO @Job (JobName)
SELECT j.NAME
FROM msdb.dbo.sysjobs AS j
WHERE j.[enabled] = 1
ORDER BY j.NAME ASC

WHILE @JobName IS NOT NULL
BEGIN
	SET @JobName = (
			SELECT MIN(JobName)
			FROM @Job
			WHERE JobName > @JobName
			)
	SET @Sql = '
EXEC msdb.dbo.sp_start_job @job_name = ' + '''' + @JobName + '''' + '; 
'
	PRINT @Sql
END

How to get SQL to remove non-numeric characters from a field

sql, SQL Server, T-SQL, Uncategorized, , , , , , , ,

So I was working with phone numbers recently and the field was filthy. I mean there was absolutely no data entry validation whatsoever. Everything has been entered into this field from email addresses entered accidentally to phone numbers with little notes like (This is Steve’s number) to just random characters. There are millions of legitimate numbers in this field so in order to make the field workable the junk has to be taken out first.

A colleague of mine was kind enough to share this script with me for replacing characters. I’ve found it to be very beneficial as it is much more efficient than the solution I had been using of replacing all junk characters in a column row by row. This approach is set based replacing a single unwanted character from an entire column at a time.

CREATE TABLE #temp ([FreeText] VARCHAR(50) NOT NULL)

INSERT #temp
VALUES ('Hi There!!! 222  10 - 3476')

INSERT #temp
VALUES ('p@yahoo.com $%*$& 1234567 $%^&^')

DECLARE @DisallowedCharsASCIICodeMin TINYINT
	,@DisallowedCharsASCIICodeMax TINYINT
	,@ASCIICode TINYINT
	,@Char CHAR(1)
	,@ReplaceChar CHAR(1)
DECLARE @DisallowedChars TABLE (
	[ASCIICode] TINYINT NOT NULL
	,[Char] CHAR(1) NOT NULL
	,[ReplaceChar] CHAR(1) NULL
	,[Replaced] BIT NOT NULL DEFAULT 0
	,PRIMARY KEY ([ASCIICode])
	)

SELECT @DisallowedCharsASCIICodeMin = 32
	,@DisallowedCharsASCIICodeMax = 126

SET @ASCIICode = @DisallowedCharsASCIICodeMin

WHILE @ASCIICode <= @DisallowedCharsASCIICodeMax
BEGIN
	INSERT @DisallowedChars (
		[ASCIICode]
		,[Char]
		)
	VALUES (
		@ASCIICode
		,CHAR(@ASCIICode)
		)

	SET @ASCIICode = @ASCIICode + 1
END

DELETE @DisallowedChars
WHERE [Char] IN (
		'0'
		,'1'
		,'2'
		,'3'
		,'4'
		,'5'
		,'6'
		,'7'
		,'8'
		,'9'
		)

WHILE EXISTS (
		SELECT 1
		FROM @DisallowedChars
		WHERE [Replaced] = 0
		)
BEGIN
	SELECT TOP 1 @ASCIICode = [ASCIICode]
		,@Char = [Char]
	FROM @DisallowedChars
	WHERE [Replaced] = 0

	UPDATE #temp
	SET [FreeText] = CASE 
			WHEN @ReplaceChar IS NULL
				THEN REPLACE([FreeText], @Char, '')
			ELSE REPLACE([FreeText], @Char, @ReplaceChar)
			END
	WHERE [FreeText] LIKE '%' + @Char + '%'

	UPDATE @DisallowedChars
	SET [Replaced] = 1
	WHERE [ASCIICode] = @ASCIICode
END

SELECT *
FROM #temp

DROP TABLE #temp
GO

 

For my needs I’ve turned this logic into a stored procedure. I’ve also made a few changes. Firstly I’ve expanded the range of charters to exclude from unprintable characters to additional Unicode characters. I’ve also removed the option to add in a replace character as I only want non-numeric characters to be removed from the field. This allows me to also remove the case statement.

-- Drop stored procedure if it already exists
IF EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.ROUTINES
		WHERE SPECIFIC_SCHEMA = N'dbo'
			AND SPECIFIC_NAME = N'GetNumeric'
		)
	DROP PROCEDURE dbo.GetNumeric
GO
-- Create Procedure
CREATE PROCEDURE dbo.GetNumeric @TableName VARCHAR(255)
	,@ColumnName VARCHAR(255)
AS
SET NOCOUNT ON

DECLARE @sql AS VARCHAR(500)

CREATE TABLE #temp (
	DirtyColumn VARCHAR(255) NOT NULL
	,CleanColumn VARCHAR(255) NOT NULL
	)

--SELECT @TableName
SET @sql = 'SELECT ' + @ColumnName + ' AS DirtyColumn
,' + @ColumnName + ' AS CleanColumn FROM ' + @TableName

INSERT INTO #temp (
	DirtyColumn
	,CleanColumn
	)
EXEC (@sql)

DECLARE @DisallowedCharsASCIICodeMin TINYINT
	,@DisallowedCharsASCIICodeMax TINYINT
	,@ASCIICode TINYINT
	,@Char CHAR(1)
	,@ReplaceChar CHAR(1)
DECLARE @DisallowedChars TABLE (
	[ASCIICode] TINYINT NOT NULL
	,[Char] CHAR(1) NOT NULL
	,[ReplaceChar] CHAR(1) NULL
	,[Replaced] BIT NOT NULL DEFAULT 0
	,PRIMARY KEY ([ASCIICode])
	)

SELECT @DisallowedCharsASCIICodeMin = 1
	,@DisallowedCharsASCIICodeMax = 254

SET @ASCIICode = @DisallowedCharsASCIICodeMin

WHILE @ASCIICode <= @DisallowedCharsASCIICodeMax
BEGIN
	INSERT @DisallowedChars (
		[ASCIICode]
		,[Char]
		)
	VALUES (
		@ASCIICode
		,CHAR(@ASCIICode)
		)

	SET @ASCIICode = @ASCIICode + 1
END

DELETE @DisallowedChars
WHERE [Char] IN (
		'0'
		,'1'
		,'2'
		,'3'
		,'4'
		,'5'
		,'6'
		,'7'
		,'8'
		,'9'
		)

WHILE EXISTS (
		SELECT 1
		FROM @DisallowedChars
		WHERE [Replaced] = 0
		)
BEGIN
	SELECT TOP 1 @ASCIICode = [ASCIICode]
		,@Char = [Char]
	FROM @DisallowedChars
	WHERE [Replaced] = 0

	UPDATE #temp
	SET [CleanColumn] = REPLACE([CleanColumn], @Char, '')
	WHERE [CleanColumn] LIKE '%' + @Char + '%'

	UPDATE @DisallowedChars
	SET [Replaced] = 1
	WHERE [ASCIICode] = @ASCIICode
END

SET @sql = 'UPDATE T1
SET T1.' + @ColumnName + ' = T2.CleanColumn
FROM ' + @TableName + ' AS T1
INNER JOIN #temp AS T2
ON T1.' + @ColumnName + ' = T2.DirtyColumn
WHERE T1.' + @ColumnName + '= T2.DirtyColumn;'

EXEC (@sql)

DROP TABLE #temp
GO


GO

 

After deploying the SP above to your test environment you can test it with the following script.

IF OBJECT_ID('dbo.DirtyPhoneNumbers', 'U') IS NOT NULL
	DROP TABLE dbo.DirtyPhoneNumbers;

CREATE TABLE DirtyPhoneNumbers (PhoneNumbers VARCHAR(255));
GO

INSERT INTO DirtyPhoneNumbers (PhoneNumbers)
VALUES ('afef2313newfnaksdfn')
	,('afef2313n!!!!!!!!!!&dfn')
	,('afef====+++22221sdfn')
	,('afef!"£$%^&&7575757sdfn')

SELECT *
FROM DirtyPhoneNumbers

EXEC dbo.GetNumeric 'DirtyPhoneNumbers'
	,'PhoneNumbers'

SELECT *
FROM DirtyPhoneNumbers

How to turn a month name and year into a date field in SQL

sql, SQL Server, SSRS, SSRS Reporting, T-SQL, , , , , , , , , , , ,

When would I use this?

Say you have an SSRS report which provides users with the parameter options month and year and the report returns data for that month and year. You will likely need that month name (varchar) and year (int) to be converted into a date fields representing the first and last day of that month/year. Once you have those two dates they can then be used in the WHERE clause of your SQL query to return data within that range. The logic below will allow the conversion of month name and year into start and end dates described above. You can now take this logic and input it into a stored procedure or user defined function.

There will only ever be 12 months going forward but to create an ever updating parameter option for years please see this tutorial.

DECLARE @year AS INT
DECLARE @month AS VARCHAR(9)
DECLARE @monthNumber AS CHAR(2)
DECLARE @startDate AS DATE
DECLARE @endDate AS DATE;

SET @year = 2016
SET @month = 'February'

IF @year IS NOT NULL
BEGIN
	WITH monthPicker
	AS (
		SELECT CASE 
				WHEN @month = 'January'
					THEN '01'
				WHEN @month = 'February'
					THEN '02'
				WHEN @month = 'March'
					THEN '03'
				WHEN @month = 'April'
					THEN '04'
				WHEN @month = 'May'
					THEN '05'
				WHEN @month = 'June'
					THEN '06'
				WHEN @month = 'July'
					THEN '07'
				WHEN @month = 'August'
					THEN '08'
				WHEN @month = 'September'
					THEN '09'
				WHEN @month = 'October'
					THEN '10'
				WHEN @month = 'November'
					THEN '11'
				WHEN @month = 'December'
					THEN '12'
				ELSE NULL
				END AS monthPicked
		)
	SELECT @monthNumber = (
			SELECT monthPicked
			FROM monthPicker
			)

	SET @startDate = (
			SELECT CAST(CAST(@year AS VARCHAR(4)) + @monthNumber + '01' AS DATETIME)
			)
	SET @endDate = (
			SELECT DATEADD(s, - 1, DATEADD(MM, DATEDIFF(M, 0, @startDate) + 1, 0))
			)
END
ELSE
BEGIN
	SET @startDate = (
			SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
			)
	SET @endDate = (DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, GETDATE()), - 1))
END

PRINT @startDate
PRINT @endDate

How to pass table valued parameters in SQL Server

SQL Server, T-SQL, , , , , , ,

With the introduction of SQL Server 2008 came the ability to define an entire table as a parameter, think of it like a table data type. This feature greatly eases the development process as constructing and parsing XML data strings is no longer necessary.

Table parameters are user defined parameters, i.e. you are creating a means of storing specific data that is passed by a stored procedure or function.

Limitations:

  • The READONLY clause must be used when passing in the table valued variable
  • Data in the table variable cannot be modified
  • The table variables cannot be used as OUTPUT parameters only input parameters.
  • When data is passed to the table variable the table variable must be passed to the stored procedure in the same batch. Table variables go out of scope as soon as the procedure or batch returns.

The following is a complete end to end example of how to create and pass data to table parameters:

IF OBJECT_ID('OrderHistory') > 0
	DROP TABLE OrderHistory;
GO

CREATE TABLE [dbo].[OrderHistory] (
	[OrderID] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY
	,[Product] [varchar](10) NULL
	,[OrderDate] [datetime] NULL
	,[SalePrice] [money] NULL
	)
GO

CREATE TYPE OrderHistoryTableType AS TABLE (
	[Product] [varchar](10) NULL
	,[OrderDate] [datetime] NULL
	,[SalePrice] [money] NULL
	)
GO

CREATE PROCEDURE usp_InsertOrder (@TableVariable OrderHistoryTableType READONLY)
AS
BEGIN
	INSERT INTO OrderHistory (
		Product
		,OrderDate
		,SalePrice
		)
	SELECT Product
		,OrderDate
		,SalePrice
	FROM @TableVariable
END
GO

DECLARE @DataTable AS OrderHistoryTableType

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Desktop'
	,GETUTCDATE()
	,599.00
	)

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Laptop'
	,GETUTCDATE()
	,299.00
	)

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Mouse'
	,GETUTCDATE()
	,9.00
	)

EXEC usp_InsertOrder @TableVariable = @DataTable

SELECT *
FROM OrderHistory

 

If you want to view other types of table type definitions in your system, or you’ve forgotten what you called a specific table parameter, you can execute the following query, which looks in the system catalog:

SELECT * FROM sys.table_types

SQL table of common file types and extensions used in business

sql, T-SQL, Uncategorized, , , , , , , ,

Below is a list of the file types and their respective extensions commonly used in business. You can use the SQL script on this page to create a table for use in queries and stored procedures.

(The table below was created used No-Cruft Excel to HTML Table Converter)

FileType Extension
Microsoft Word 97 – 2003 Document doc
Microsoft Word 97 – 2003 Template dot
Word document docx
Word macro-enabled document docm
Word template dotx
Word macro-enabled template dotm
Word binary document introduced in Microsoft Office 2007 docb
Microsoft Excel 97-2003 Worksheet xls
Microsoft Excel 97-2003 Template xlt
Excel macro xlm
Excel workbook xlsx
Excel macro-enabled workbook xlsm
Excel template xltx
Excel macro-enabled template xltm
Excel binary worksheet xlsb
Excel add-in or macro xla
Excel add-in xlam
Excel XLL add-in xll
Excel workspace xlw
Legacy PowerPoint presentation ppt
Legacy PowerPoint template pot
Legacy PowerPoint slideshow pps
PowerPoint presentation pptx
PowerPoint macro-enabled presentation pptm
PowerPoint template potx
PowerPoint macro-enabled template potm
PowerPoint add-in ppam
PowerPoint slideshow ppsx
PowerPoint macro-enabled slideshow ppsm
PowerPoint slide sldx
PowerPoint macro-enabled slide sldm
The file extension for the Office Access 2007 file format ACCDB
The file extension for Office Access 2007 files that are in “execute only” mode ACCDE
The file extension for Access Database Templates. ACCDT
The file extension for the Office Access 2007 file format that enables you to open a database in runtime mode ACCDR
Microsoft Publisher file extension pub
Windows BitMap BMP
Data Interchange format DIF
Graphics Interchange Format GIF
Web page source text HTML
JPEG graphic JPG
JPEG graphic JPEG
Web page imagemap MAP
Acrobat -Portable document format PDF
Public Network graphic PNG
Adobe PhotoShop PSD
PaintShop Pro PSP
Rich Text Format RTF
Stuffit Compressed Archive SIT
UNIX TAR Compressed Archive TAR
TIFF graphic TIF
ASCII text (Mac text does not contain line feeds–use DOS Washer Utility to fix) TXT
Windows sound WAV
MS Works WKS
PC Zip Compressed Archive ZIP

 

USE [YourDatabase]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IH_FileType](
	[IH_FileType_ID] [int] IDENTITY(1,1) NOT NULL,
	[FileType] [varchar](250) NULL,
	[Extension] [varchar](5) NULL,
	[OfficeFileType] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[IH_FileType_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[IH_FileType] ON
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (1, N'Microsoft Word 97 - 2003 Document', N'doc', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (2, N'Microsoft Word 97 - 2003 Template', N'dot', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (3, N'Word document', N'docx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (4, N'Word macro-enabled document', N'docm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (5, N'Word template', N'dotx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (6, N'Word macro-enabled template', N'dotm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (7, N'Word binary document introduced in Microsoft Office 2007', N'docb', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (8, N'Microsoft Excel 97-2003 Worksheet', N'xls', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (9, N'Microsoft Excel 97-2003 Template', N'xlt', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (10, N'Excel macro', N'xlm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (11, N'Excel workbook', N'xlsx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (12, N'Excel macro-enabled workbook', N'xlsm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (13, N'Excel template', N'xltx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (14, N'Excel macro-enabled template', N'xltm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (15, N'Excel binary worksheet', N'xlsb', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (16, N'Excel add-in or macro', N'xla', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (17, N'Excel add-in', N'xlam', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (18, N'Excel XLL add-in', N'xll', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (19, N'Excel workspace', N'xlw', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (20, N'Legacy PowerPoint presentation', N'ppt', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (21, N'Legacy PowerPoint template', N'pot', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (22, N'Legacy PowerPoint slideshow', N'pps', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (23, N'PowerPoint presentation', N'pptx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (24, N'PowerPoint macro-enabled presentation', N'pptm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (25, N'PowerPoint template', N'potx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (26, N'PowerPoint macro-enabled template', N'potm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (27, N'PowerPoint add-in', N'ppam', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (28, N'PowerPoint slideshow', N'ppsx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (29, N'PowerPoint macro-enabled slideshow', N'ppsm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (30, N'PowerPoint slide', N'sldx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (31, N'PowerPoint macro-enabled slide', N'sldm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (32, N'The file extension for the Office Access 2007 file format', N'accdb', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (33, N'The file extension for Office Access 2007 files that are in "execute only" mode', N'accde', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (34, N'The file extension for Access Database Templates.', N'accdt', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (35, N'The file extension for the Office Access 2007 file format that enables you to open a database in runtime mode', N'accdr', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (36, N'Microsoft Publisher file extension', N'pub', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (37, N'Windows BitMap', N'bmp', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (38, N'Data Interchange format', N'dif', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (39, N'Graphics Interchange Format', N'gif', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (40, N'Web page source text', N'html', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (41, N'JPEG graphic', N'jpg', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (42, N'JPEG graphic', N'jpeg', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (43, N'Web page imagemap', N'map', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (44, N'Acrobat -Portable document format', N'pdf', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (45, N'Public Network graphic', N'png', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (46, N'Adobe PhotoShop', N'psd', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (47, N'PaintShop Pro', N'psp', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (48, N'Rich Text Format', N'rtf', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (49, N'Stuffit Compressed Archive', N'sit', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (50, N'UNIX TAR Compressed Archive', N'tar', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (51, N'TIFF graphic', N'tif', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (52, N'ASCII text (Mac text does not contain line feeds--use DOS Washer Utility to fix)', N'txt', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (53, N'Windows sound', N'wav', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (54, N'MS Works', N'wks', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (55, N'PC Zip Compressed Archive', N'zip', 0)
SET IDENTITY_INSERT [dbo].[IH_FileType] OFF


SELECT *
FROM IH_FileType
Image with the text 3000 years later in giant letters

How to provide a dynamic parameter drop-down of year options in SSRS

Reporting, , , , , , , , , , , , ,

Say you’ve developed a report which returns data based on a inputted year parameter value, e.g. “give me all the sales in 2014”.

How do you provide the year options for the SSRS report?

Well there’s three ways that come to mind.

Select distinct from a date field, e.g. SELECT DISTINCT YEAR(SalesDate) FROM Sales

This would certainly provide you with all the available years but the database could have millions of sales. So it’s not too efficient.

You could manually populate years in the parameter settings in the SSRS report, kind of primitive but it would work.

But for me the robust and efficient way is the solution provided below.

The following SQL query dynamically populates an integer field in a temporary table with years. The query uses a base year variable, which can be set to as far back as when the required data fields and values existed in the database. A loop then provides the years up to and including the current year. This query can be used to generate a dataset for an SSRS report and then this dataset can then be used to provide parameter values for the report. The report will then always create a list of years between the base year and the current year. Meaning the years parameter will never need to be adjusted again.

(For a tutorial on how to turn a month name and year into dates for the first and last day of the month see this tutorial)

/*Create temp table populated with the years from a base year to the present year*/
IF OBJECT_ID('tempdb..#availableYear') IS NOT NULL
    DROP TABLE #availableYear

CREATE TABLE #availableYear ([Year] INT)

DECLARE @baseYear AS INT
DECLARE @i AS INT

/*Change the base year to the earliest year the database has the required data available*/
SET @baseYear = 2013
SET @i = 0

WHILE @i <= YEAR(GETDATE()) - @baseYear
BEGIN
    INSERT INTO #availableYear
    SELECT @baseYear + @i

    SET @i = @i + 1
END

SELECT * FROM #availableYear

Within the Stored Procedure that populates the report you can then do something like below to make sure the date range matches the year chosen.

DECLARE @yearChosen AS INT

SET @yearChosen = 2013

DECLARE @startDate date
DECLARE @endDate date

SET @startDate = CONVERT(CHAR(4), @yearChosen) + '0101'
SET @endDate = CONVERT(CHAR(4), @yearChosen) + '1231'

PRINT @startDate
PRINT @endDate    

--OR For example

YEAR(SaleDate) = @yearChosen

How to write text to File in SQL Server Management Studio

SQL Server, , , , , , , , , ,

The following is a tutorial on creating a stored procedure that will allow you to create a file in any directory and insert text into this newly created file.

In order for this to work you will need to authorise the running of system stored procedures with Object Linking and Embedding functionality (See OLE).

Authorisation is needed as the stored procedure we will create rely on the system SPs sp_OACreate and sp_OAMethod.

sp_OACreate: Creates an instance of an OLE object.

sp_OAMethod: Calls a method of an OLE object.

NOTE: By default, SQL Server blocks access to OLE Automation stored procedures by turning the components off as part of the security configuration for the server.

Run the script below to grant authorisation.

--AUTHORIZE SYSTEM STORED PROCEDURES
sp_configure 'show advanced options'
	,1;
GO

RECONFIGURE;
GO

sp_configure 'Ole Automation Procedures'
	,1;
GO

RECONFIGURE;
GO

--AUTHORIZED

 
Next we will create the stored procedure WriteToFile. Substitute the DatabaseName with the database you will be using.

--CREATE STORED PROCEDURE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[WriteToFile]', 'P') IS NOT NULL
	DROP PROCEDURE [WriteToFile];
GO

CREATE PROCEDURE [dbo].[WriteToFile] @File VARCHAR(255)
	,@Text VARCHAR(MAX)
	WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @OLE INT
	DECLARE @FileID INT

	EXECUTE sp_OACreate 'Scripting.FileSystemObject'
		,@OLE OUT

	EXECUTE sp_OAMethod @OLE
		,'OpenTextFile'
		,@FileID OUT
		,@File
		,8
		,1

	EXECUTE sp_OAMethod @FileID
		,'WriteLine'
		,NULL
		,@Text

	EXECUTE sp_OADestroy @FileID

	EXECUTE sp_OADestroy @OLE
END;

 
Below is an example using the WriteToFile stored procedure.

--WRITE TO FILE EXAMPLE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'
SET @Txt = 'Hello World'

EXEC [DatabaseName].[dbo].[WriteToFile] @Path
	,@Txt;

 

Using the same stored procedure here’s an example writing a table to the file using concatenation and a loop. This process works by taking the table row by row and writing the concatenated value to the file. There are easier ways to achieve this however, i.e. utilising the export functionality built into SSMS and saving this as a package to be run as a job.

--STORED PROCEDURE CREATED
--WRITE TABLE TO FILE EXAMPLE
DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)
DECLARE @loopId AS INT
DECLARE @maxId AS INT
DECLARE @TempCustomer TABLE (
	ID_column INT IDENTITY PRIMARY KEY
	,FirstName NVARCHAR(100)
	);

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'

INSERT INTO @tempCustomer (FirstName)
VALUES ('Paul')
	,('Jim')
	,('John')

SET @loopId = 1
SET @maxId = (
		SELECT MAX(ID_column)
		FROM @TempCustomer
		)

WHILE @loopId <= @maxId
BEGIN
	SELECT @Txt = CONVERT(VARCHAR(10), ID_column) + ', ' + FirstName
	FROM @TempCustomer
	WHERE ID_column = @loopId

	PRINT @Txt

	EXEC [TEST_DB].[dbo].[WriteToFile] @Path
		,@Txt;

	SET @loopId = @loopId + 1
END

 

How to export a table as an XML file using a SQL script

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , , , , ,

Below is a script that will allow you, as the title suggests, to export a table as an XML file.

By use of the find and replace function in your preferred text editor, or by way of the text editor in SSMS, input your values for the required fields, i.e. replace TableName with the name of the table you will be working off.

SERVER NAME: ServerName

DATABASE NAME: DatabaseName

SCHEMA NAME: SchemaName

TABLE NAME: TableName

SAVE LOCATION ADDRESS: AddressName

FILE NAME: FileName (Excluding .xml extension)

Then run the script below and the table will be exported as an XML file to the location and file name of your choosing.

/*
FIND AND REPLACE:

SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
SCHEMA NAME: SchemaName
TABLE NAME: TableName
SAVE LOCATION ADDRESS: AddressName
FILE NAME: FileName
 */
-- Run on ServerName
USE DatabaseName;
GO

DECLARE @saveLocation AS VARCHAR(510)
DECLARE @nameOfFile AS VARCHAR(255)
DECLARE @instanceName AS VARCHAR(255)
DECLARE @cmd NVARCHAR(1020)

SET @saveLocation = 'AddressName'
SET @nameOfFile = 'FileName'
SET @saveLocation = @saveLocation + '\' + @nameOfFile + '.xml'
SET @instanceName = (
		SELECT @@servername
		)

-- Please note that the fully qualified table name is needed
SELECT @cmd = '
    bcp "SELECT * FROM [DatabaseName].[SchemaName].[TableName] row for xml auto, root(''rows''), elements" ' + 'queryout "' + @saveLocation + '" -S ' + @instanceName + ' -T -w -r -t';

EXEC xp_cmdshell @cmd;
GO

 

This script is enabled by two utilities xp_cmdshell and BCP.

For an explanation of each please view the associated links.