Archive for the ‘T-SQL’ Category

T-SQL: Backup All User Databases

Wednesday, August 18th, 2010

Here’s an easy, generic way to back up all user databases on a SQL Server instance:

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\localBackups\' -- has to be a local path

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Hope this helps!

Posted in MS SQL Server, T-SQL | No Comments »

Stored Procedure to Export a Table via BCP

Wednesday, August 4th, 2010

If you are looking for a generic way to export table data via BCP, here’s how:

CREATE PROCEDURE ExportDataViaBcp
(
 @server VARCHAR(100) = '(local)',
 @db VARCHAR(100),
 @dbtable VARCHAR(100),  
 @file VARCHAR(200),
 @fmt_date INT = 121,
 @fmt_decimal INT = 128,
 @fmt_money INT = 1,
 @fmt_float INT = NULL
)
AS

SET NOCOUNT ON

-- sql that gets executed 
DECLARE @tmpSql VARCHAR(8000) 

-- in case we have an error
DECLARE @error VARCHAR(200)

-- USE the database
SELECT @tmpSql = 'USE ' + @db + ';'
PRINT @tmpSql
EXEC(@tmpSql)

-- Make sure the (global) temp tables have been cleaned up
IF OBJECT_ID('tempdb..##export_temp') IS NOT NULL
 DROP TABLE ##export_temp
IF OBJECT_ID('tempdb..##export_temp2') IS NOT NULL
 DROP TABLE ##export_temp2

-- Execute the use and the select
SELECT @tmpSql = 'SELECT * INTO ##export_temp FROM ' + @dbtable  + ';'
PRINT @tmpSql
EXEC(@tmpSql)

-- Build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar from their original type
DECLARE
 @columnNames VARCHAR(MAX),
 @columnConversions VARCHAR(MAX)
SELECT
 @columnNames =
  COALESCE(@columnNames + ',', '') +
  '[' + column_name + ']',
 @columnConversions =
  COALESCE(@columnConversions  + ',', '') +
  'CONVERT(NVARCHAR(100),[' + column_name + ']' +
  CASE
   WHEN data_type IN ('NUMERIC', 'DECIMAL') THEN CASE WHEN @fmt_decimal IS NOT NULL THEN ',' + CONVERT(VARCHAR, @fmt_decimal) ELSE '' END
   WHEN data_type IN ('MONEY', 'SMALLMONEY') THEN CASE WHEN @fmt_money IS NOT NULL THEN ',' + CONVERT(VARCHAR, @fmt_money) ELSE '' END
   WHEN data_type IN ('DATETIME', 'SMALLDATETIME') THEN CASE WHEN @fmt_date IS NOT NULL THEN ',' +  + CONVERT(VARCHAR, @fmt_date) ELSE '' END
   WHEN data_type IN ('FLOAT', 'REAL') THEN CASE WHEN @fmt_float IS NOT NULL THEN ',' +  + CONVERT(VARCHAR, @fmt_float) ELSE '' END
   ELSE ''
  END + ') AS [' + column_name + ']'
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '##export_temp'

-- execute select query to insert data and column names into new temp table
SELECT @tmpSql =
  'SELECT ' + @columnNames + ' ' +
  'INTO ##export_temp2 ' +
  'FROM ( ' +
  ' SELECT ' + @columnConversions + ', 2 AS tmpsort FROM ##export_temp ' +
  ' UNION ALL ' +
  ' SELECT ''' + REPLACE(REPLACE(REPLACE(@columnNames, ',', ''', '''),'[',''),']','') + ''', 1 AS tmpsort ' +
  ' ) AS x ' +
  ' ORDER BY x.tmpsort; ';

PRINT @tmpSql
EXEC(@tmpSql)

-- Execute the BCP to create the file
SELECT @tmpSql = 'bcp "SELECT * FROM ##export_temp2" queryout "' + @file + '" -c -CRAW -t, -T -S' + @server
EXEC master.sys.xp_cmdshell @tmpSql
IF @@ERROR > 0
BEGIN
 IF OBJECT_ID('##export_temp') IS NOT NULL
  DROP TABLE ##export_temp
 IF OBJECT_ID('##export_temp2') IS NOT NULL
  DROP TABLE ##export_temp2
 SELECT @error = 'Couldn''t execute the bcp: ''' + @tmpSql;
 RAISERROR(@error, 16, 1)
 RETURN
END

-- Make sure the (global) temp tables have been cleaned up
IF OBJECT_ID('tempdb..##export_temp') IS NOT NULL
 DROP TABLE ##export_temp
IF OBJECT_ID('tempdb..##export_temp2') IS NOT NULL
 DROP TABLE ##export_temp2

GO

This does use global temp tables as well as xp_cmdshell, but it also gets the job done. 

This is a modified version of the suggestion found here:
http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx

Thanks to them for actually doing the hard work!  I just cleaned it up and made it support varied column names.

Posted in T-SQL | No Comments »

Shrinking your Transaction Log with SQL Server 2008 to Free Disk Space

Friday, February 5th, 2010

If you have been used to clearing up disk space on your development and test SQL server instance file systems with SQL Server 2000 or SQL Server 2005 by using the famous TRUNCATEONLY option on your transaction logs, you might be disheartened that they have removed this option from SQL Server 2008 (with good reason some might say).

If you do try to use it, you will get the infamous:

‘TRUNCATEONLY’ is not a recognized BACKUP option.

To achieve the same effect with SQL Server 2008, you can toggle the recovery mode for the target database and then call your DBCC SHRINKFILE to clear up disk space hogged by your transaction log.

USE MyDatabase
GO
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE('MyDatabase_log',1)
GO
ALTER DATABASE MyDatabase SET RECOVERY FULL
GO

(Of course if you are already using simple recovery, you wouldn’t need to toggle.)

Hope this helps!

Posted in MS SQL Server, T-SQL | No Comments »

Don’t get caught by ANSI PADDING, VARCHAR, and trailing whitespace!

Thursday, January 28th, 2010

One thing to be aware of when you have VARCHAR columns and are using ANSI PADDING: trailing whitespace is trimmed and not counted in your equality ( = / <> / LIKE) statements, so you might not get the results you are looking to be returned from your query.

In the same vein, the len() function will return the same value for two strings if one has trailing whitespace.  If you need to compare those two fields including trailing whitespace values, use datalength() instead.

Here’s some example T-SQL:

DECLARE @TABLE TABLE (FIELD VARCHAR(50))

INSERT @TABLE VALUES ('some text')
INSERT @TABLE VALUES ('some text       ')
INSERT @TABLE VALUES ('    some text')

-- returns 2 :
-- trailing spaces trimmed,
-- leading spaces count
SELECT '"' + FIELD + '"'
FROM @TABLE
WHERE FIELD = 'some text'

-- also returns 2
SELECT '"' + FIELD + '"'
FROM @TABLE
WHERE FIELD LIKE 'some text'

-- shows the difference between len()
-- and datalength()
SELECT FIELD, LEN(FIELD) AS 'Length', DATALENGTH(FIELD) AS 'DataLength'
FROM @TABLE

Hope this helps!

Posted in MS SQL Server, T-SQL | No Comments »

T-SQL: Change Collation on your LIKE Clause Using COLLATE

Friday, July 24th, 2009

Ever have trouble matching unicode letter equivalents in your T-SQL queries? For example, when you want “Québec” and “Quebec” to be equivalent in your text search query?

This is where specifying Accent Insensative Collation (The “AI” in all those collation codes) comes to the rescue.  If your database is already set to an AI collation, then you are all set already, but if not, change your LIKE statement to use something like the following:

SELECT *
FROM MyTable (NOLOCK)
WHERE MyColumn COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%quebec%'

Hope this helps!

Posted in MS SQL Server, T-SQL | No Comments »