tech/music/movies/musings
Show MenuHide Menu

Tag: T-SQL

Instant SQL Formatter

January 17, 2014

image Useful online tool for formatting T-SQL code which also generates the same code in HTML.

Available here: http://www.dpriver.com/pp/sqlformat.htm

Use Openrowset to select from Excel into SQL Server

April 29, 2012
SELECT *
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;Database=C:\test.xls'
,'SELECT * FROM [Sheet1$]')

Or from CSV:

SELECT * FROM
OPENROWSET
('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:\;Extensions=CSV;','SELECT * FROM my_csv.csv')

sql

Import Fixed Width Text File into SQL Server

April 29, 2012

Had a torrid time trying to get SSIS working to import a fixed width text file into SQL server. In the end due to lack of flexibility with SSIS packages I opted to revert to using schema files instead. This worked perfectly, but the only downside is that there has to be a unique schema file per text file if the fixed width layout is different.

This article from Datebase Journal is all you need to get started. Also there are some useful functions in this article by Phil Factor

text-15

‘CREATE SCHEMA’ must be the first statement in a query batch

December 11, 2011
If you receive this error when trying to create a schema in a stored procedure try this instead:

exec(‘use db; exec sp_executesql N”create schema test”’)

sql

T-SQL: Change column type

May 18, 2011

ALTER TABLE MAIN
ALTER COLUMN [natFileID] NVARCHAR(30) NULL

sql

T-SQL to drop all non-system databases in SQL server instance

May 5, 2011
EXEC sp_MSforeachdb '
IF DB_ID(''?'') > 4
BEGIN
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE ?
END'
sql
 

Return All Values in a Column as a Delimited List

April 22, 2011

USE AdventureWorks
GO

DECLARE @employeeIDs NVARCHAR(1000)
DECLARE @delimiter NVARCHAR(1)

        SET @employeeIDs = ”
        SET @delimiter = ‘;’

        SELECT @employeeIDs =
            COALESCE(
                    CASE WHEN @employeeIDs = ”
                        THEN CAST(EmployeeID AS NVARCHAR(5))
                        ELSE @employeeIDs + @delimiter + CAST(EmployeeID AS NVARCHAR(5))
                    END, ”)
        FROM HumanResources.Employee

PRINT @employeeIDs

sql
   

T-SQL function to return name of log file for a database

February 25, 2011

ALTER FUNCTION [dbo].[UDF_DB_LOG_FILE_NAME]

(

      @dbName VARCHAR(100)

)

RETURNS VARCHAR(100)

AS

— #author Joe Hunter

— #version 1.0

— #desc Return the name of the log file (.LDF) for a database

— #param @dbName The database name

BEGIN

RETURN

(

SELECT [name]

FROM sys.master_files

WHERE DB_NAME(database_id) = @dbName

AND [TYPE] = 1 –(TYPE 1 = LDF ::jh feb 2011)

)

END

sql

T-SQL Test

December 12, 2010

Tim Chapman has provided a T-SQL test to practice for any upcoming SQL Server interviews.

sql