How to Create a Quick Table Backup using T-SQL

–OPTIONALLY CHECK FOR AND DROP THE BACKUP TABLE IF ALREADY EXISTS:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MY_TABLE_BACKUP_20130710]’) AND type in (N’U’))
DROP TABLE [MY_TABLE_BACKUP_20130710]
GO

–ONE LINE TABLE BACKUP:
SELECT * INTO [MY_TABLE_BACKUP_20130710] FROM [MY_ORG_TABLE]
GO
–NOTE THIS COPIES JUST DATA, NOT CONSTRAINTS OR RELATIONS

Umbraco SQl to Find Nodes using a Certain Document Type

SELECT * FROM cmsContent C
INNER JOIN cmsContentType CT ON C.contentType = CT.nodeId
INNER JOIN umbracoNode N ON C.nodeId = N.id
WHERE CT.alias = ‘yourDocumentTypeAliasHere’

–use the node path to locate nodes in Umbraco

T-SQL TO DROP/CREATE IDENTITY SETTING ON ALL TABLES


/*
---------------------------------------------------------------------------------------------------------
PRINTS T-SQL TO DROP/CREATE IDENTITY SETTING ON ALL TABLES

BACKUP BEFORE RUNNING THE GENERATED SQL! - DROPS AND RECREATES EVERYTHING YOU SELECT.
ALSO PLEASE READ THE BELOW - THIS IS A SIMPLIFIED PURPOSE BUILT SCRIPT - YOU WILL NEED TO MODIFY TO YOUR REQUIREMENTS.

THERE IS NO WAY OF REMOVING THE IDENTITY(START, SEED) SETTING ON TABLES -
AND AS IDENTITY INSERT ON/OFF DOES NOT DO THE JOB FOR ALL ORMs (LIKE LINQ FOR EXAMPLE) WE'RE STUCK WITH THE
SQL SERVER MANAGEMENT IDE SOLUTION: CREATE TEMP TABLES - WITH/WITHOUT THE IDENTITY SETTING, MOVE THE DATA, DROP ORIGINAL AND RENAME TEMP TO ORG.

THIS IS A SIMPLIFIED VERSION THAT DOES NOT TAKE INTO CONSIDERATION FKs OR PKs - SCRIPTS TO DROP AND RECREATE THESE ARE AVAILABLE ELSEWHERE
ALSO IT ASSUMES THE PK IDENTITY IS AN INT COLUMN NAMED Id!

SQL2005 AND ABOVE ONLY (SYS VIEWS)

CONFIG:
@ADD_IDENTITY => set to true to ADD this, set to false to REMOVE this.

---------------------------------------------------------------------------------------------------------
*/

DECLARE @ADD_IDENTITY BIT
SET @ADD_IDENTITY = 0 --!!!!! SEE ABOVE!

DECLARE @TABLENAME NVARCHAR(250)
DECLARE @TABLESCHEMA NVARCHAR(250)
DECLARE @TSQL NVARCHAR(MAX)
DECLARE @COLS NVARCHAR(MAX)

DECLARE DBO_TABLE_CURSOR CURSOR

READ_ONLY
FOR SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
    WHERE
            TABLE_NAME NOT LIKE 'Version%'
        AND TABLE_NAME NOT LIKE 'sys%'
        AND TABLE_SCHEMA = 'dbo'
        AND TABLE_TYPE = 'BASE TABLE'
    ORDER BY TABLE_NAME

OPEN DBO_TABLE_CURSOR
FETCH NEXT FROM DBO_TABLE_CURSOR INTO @TABLENAME, @TABLESCHEMA
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
        SET @TSQL = ''
        
        --Get rid of default constraints:
        SELECT @TSQL = @TSQL + '    
        ALTER TABLE dbo.' + @TABLENAME +'
            DROP CONSTRAINT ' + DF.[NAME] + '
        GO' FROM
            SYS.DEFAULT_CONSTRAINTS DF
                INNER JOIN SYS.ALL_COLUMNS AC
                    ON        DF.PARENT_OBJECT_ID = AC.OBJECT_ID
                        AND DF.PARENT_COLUMN_ID = AC.COLUMN_ID
        WHERE OBJECT_NAME(PARENT_OBJECT_ID) = @TABLENAME

        --Create temp table:
        SET @TSQL = @TSQL + '
        
        CREATE TABLE dbo.Tmp_' + @TABLENAME + '
        (
        '        
        SELECT @TSQL = @TSQL + '    [' + COLUMN_NAME + '] ' + UPPER(DATA_TYPE)
            + ISNULL(('(' + CONVERT(NVARCHAR,CHARACTER_MAXIMUM_LENGTH) + ') '), ' ') --Bits and Timestamps have null in all three, something added to null, is null - so we ISNULL check the whole evalutation, and add a space for bits and timestamps.
            + CONVERT(NVARCHAR, REPLACE(REPLACE(IS_NULLABLE, 'YES', 'NULL'), 'NO', 'NOT NULL'))            
            + ISNULL(' CONSTRAINT [DF_' + @TABLENAME + '_' + COLUMN_NAME + '] DEFAULT (' + CONVERT(NVARCHAR, COLUMN_DEFAULT) + ')', '')
            + ',
        '
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
                TABLE_NAME = @TABLENAME
            AND TABLE_SCHEMA = @TABLESCHEMA
        ORDER BY ORDINAL_POSITION ASC

        --Remove trailing comma:
        SET @TSQL = SUBSTRING(@TSQL, 1, LEN(@TSQL) -5) --remove trailing tabs, linebreak and finally the comma
        
        SET @TSQL = @TSQL + '    
        ) ON [PRIMARY]
        GO'
        
        --So Microsoft seem to have hacked the old NVARCHAR(MAX) - the CHARACTER_MAXIMUM_LENGTH is a int column - so they've added -1 here
        SET @TSQL = REPLACE(@TSQL, '(-1)', '(MAX)')

        --Finally - are we adding or removing the IDENTITY setting?
        IF @ADD_IDENTITY = 1
        BEGIN
            SET @TSQL = REPLACE(@TSQL, '[Id] INT NOT NULL,', '[Id] INT NOT NULL IDENTITY (1,1),')
        END

        PRINT @TSQL -- Half way there! Got temp tables, now let's move the data, drop the original, and rename the temp to replace the original... what could possibly go wrong?
        
        --RESET @TSQL:
        SET @TSQL = '
        IF EXISTS(SELECT * FROM dbo.' + @TABLENAME + ')
        BEGIN '
        IF @ADD_IDENTITY = 1--If so we need to set "IDENTITY_INSERT ON" on the temp tables.
        BEGIN
        SET @TSQL = @TSQL + '    
            SET IDENTITY_INSERT dbo.Tmp_' + @TABLENAME + ' ON'
        END

        --Now get all the column names:
        SET @COLS = ''
        SELECT @COLS = @COLS + '[' + COLUMN_NAME + '],'
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
                TABLE_NAME = @TABLENAME
            AND TABLE_SCHEMA = @TABLESCHEMA
            AND DATA_TYPE 'timestamp'
        ORDER BY ORDINAL_POSITION ASC
    
        --Remove trailing comma:
        SET @COLS = SUBSTRING(@COLS, 1, LEN(@COLS) -1)
        SET @TSQL = @TSQL + '
            INSERT INTO dbo.Tmp_' + @TABLENAME + ' (' + @COLS + ')
                SELECT ' + @COLS + '
            FROM dbo.' + @TABLENAME + ' WITH (HOLDLOCK TABLOCKX)
        '        

        IF @ADD_IDENTITY = 1 --If we're adding this, then we have switched IDENTITY_INSERT ON - now switch this back off (only one table at any one time in each DB)
        BEGIN
        SET @TSQL = @TSQL + '    
            SET IDENTITY_INSERT dbo.Tmp_' + @TABLENAME + ' OFF'
        END
        
        SET @TSQL = @TSQL + '
        END'

        SELECT @TSQL = @TSQL + '
        DROP TABLE dbo.' + @TABLENAME + '
        GO'
        SELECT @TSQL = @TSQL + '
        EXECUTE sp_rename N''dbo.Tmp_' + @TABLENAME +''', N''' + @TABLENAME +''', ''OBJECT''
        GO'

        PRINT @TSQL
        
END
FETCH NEXT FROM DBO_TABLE_CURSOR INTO @TABLENAME, @TABLESCHEMA
END

CLOSE DBO_TABLE_CURSOR
DEALLOCATE DBO_TABLE_CURSOR

T-SQL Deduplicator


/*
T-SQL DE-DUPLICATER
NOTE THAT THE CURSOR SELECT STATEMENT SELECTS *ALL* THE DUPLICATES
THE CURSOR THEN GIVES YOU ALL THE HIGHEST IDs, i.e. ALL THE LATEST - AND LET's YOU DO SOME ACTION ON THESE
TO SELECT ALL THE OLDEST SIMPLE SWITCH THE IF STATEMENT AROUND
ALSO NOTE THAT YOUR ACTION SHOULD OF-COURSE AFFECT THE CURSOR SELECT STATEMENT
*/
DECLARE @ID INT
DECLARE @ID2 INT

DECLARE DEDUPE_CURSOR CURSOR READ_ONLY
FOR SELECT
T1.[Id], T2.Id AS Id2
FROM [PersonSkill] T1
INNER JOIN [PersonSkill] T2
ON
ISNULL(T1.Deleted,1) = ISNULL(T2.Deleted,1)
AND T1.Skill = T2.Skill
AND T1.Person = T2.Person
AND T1.Id T2.Id
ORDER BY 1

/*
FOR SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
*/

OPEN DEDUPE_CURSOR

FETCH NEXT FROM DEDUPE_CURSOR INTO @ID, @ID2
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN

IF @ID2 > @ID BEGIN
PRINT CONVERT(NVARCHAR, @ID2)
--UPDATE [PersonSkill] SET Deleted = GetDate(), DeletedBy = 1 WHERE Id = @ID2
--PRINT 'SOFT DELETED ' + CONVERT(NVARCHAR, @ID2)
END

END
FETCH NEXT FROM DEDUPE_CURSOR INTO @ID, @ID2
END

CLOSE DEDUPE_CURSOR

DEALLOCATE DEDUPE_CURSOR

T-SQL Foreign Key (FK) Index Creator


/*
FOREIGN KEY INDEX CREATOR

THIS SCRIPT GENERATES SQL TO DROPS AND RECREATE NON-CLUSTERED INDEXES ON ALL FK COLUMNS
SET @FK_PREFIX TO MATCH THE FOREIGN KEY PREFIX IN YOUR DATABASE.

SQL2005 AND ABOVE ONLY (SYS VIEWS)
*/

DECLARE @FK_PREFIX NVARCHAR(50)
SET @FK_PREFIX = 'FK_'

DECLARE @CONSTRAINT_NAME NVARCHAR(255)
DECLARE @TABLE_SCHEMA NVARCHAR(255)
DECLARE @TABLE_NAME NVARCHAR(255)
DECLARE @COLUMN_NAME NVARCHAR(255)

DECLARE FK_INDEX_CURSOR CURSOR READ_ONLY
FOR SELECT
CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE

OPEN FK_INDEX_CURSOR
FETCH NEXT FROM FK_INDEX_CURSOR INTO @CONSTRAINT_NAME, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN

IF CHARINDEX(@FK_PREFIX, @CONSTRAINT_NAME) = 1 BEGIN
PRINT '

/****** Object: Index [IX_' + @CONSTRAINT_NAME + '] Script Date: ' + CONVERT(NVARCHAR(50), GetDate()) + ' ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + ']'') AND name = N''IX_' + @CONSTRAINT_NAME + ''')
DROP INDEX [IX_' + @CONSTRAINT_NAME + '] ON [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + '] WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX [IX_' + @CONSTRAINT_NAME + '] ON [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + ']
(
[' + @COLUMN_NAME + '] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

'

END
END
FETCH NEXT FROM FK_INDEX_CURSOR INTO @CONSTRAINT_NAME, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
END

CLOSE FK_INDEX_CURSOR
DEALLOCATE FK_INDEX_CURSOR

T-SQL string concatenation without coalesce

Based on: http://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx


SET @COLS = ''
SELECT @COLS = @COLS + '[' + COLUMN_NAME + '] ASC,'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME = @CONSTRAINT_NAME
ORDER BY COLUMN_NAME DESC

Also poss to use FOR XML PATH:

SELECT ',' + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)
FOR XML PATH('')

UNIQUE KEY INDEX AND PRIMARY KEY DROP/CREATE GENERATOR


/*
UNIQUE KEY INDEX AND PRIMARY KEY DROP/CREATE GENERATOR

THIS SCRIPT GENERATES SQL TO DROPS AND RECREATE ALL *PREVIOUSLY DEFINED* UNIQUE INDEXES ON ALL TABLES
SET @FK_PREFIX TO MATCH THE FOREIGN KEY PREFIX IN YOUR DATABASE.

SQL2005 AND ABOVE ONLY (SYS VIEWS)

----------- IMPORTANT CONFIG SETTINGS: ---------------------

SET CONSTRAINT_TYPE:
'UNIQUE'
'PRIMARY KEY'

SET GENERATE_MODE:
1 = DROP
2 = CREATE
3 = RECREATE

------------------------------------------------------------
*/
DECLARE @GENERATE_MODE INT
SET @GENERATE_MODE = 3
DECLARE @CONSTRAINT_TYPE NVARCHAR(25)
SET @CONSTRAINT_TYPE = 'PRIMARY KEY'

DECLARE @CREATE_AS NVARCHAR(25)
SET @CREATE_AS = 'UNIQUE NONCLUSTERED'
IF @CONSTRAINT_TYPE = 'PRIMARY KEY' BEGIN
SET @CREATE_AS = 'PRIMARY KEY CLUSTERED'
END

DECLARE @CONSTRAINT_NAME NVARCHAR(255)
DECLARE @TABLE_SCHEMA NVARCHAR(255)
DECLARE @TABLE_NAME NVARCHAR(255)
DECLARE @COLS NVARCHAR(255)

DECLARE FK_INDEX_CURSOR CURSOR READ_ONLY
FOR SELECT
CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = @CONSTRAINT_TYPE
AND TABLE_NAME NOT LIKE 'sys%'

OPEN FK_INDEX_CURSOR
FETCH NEXT FROM FK_INDEX_CURSOR INTO @CONSTRAINT_NAME, @TABLE_SCHEMA, @TABLE_NAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @COLS = ''
SELECT @COLS = @COLS + '[' + COLUMN_NAME + '] ASC,'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME = @CONSTRAINT_NAME
ORDER BY COLUMN_NAME DESC

IF @GENERATE_MODE = 1 OR @GENERATE_MODE = 3 BEGIN
PRINT '
/****** Object: Index [' + @CONSTRAINT_NAME + '] Script Date: ' + CONVERT(NVARCHAR(50), GetDate()) + ' ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + ']'') AND name = N''' + @CONSTRAINT_NAME + ''')
ALTER TABLE [dbo].[' + @TABLE_NAME + '] DROP CONSTRAINT [' + @CONSTRAINT_NAME + ']
GO
'
END

IF @GENERATE_MODE = 2 OR @GENERATE_MODE = 3 BEGIN
IF @GENERATE_MODE = 2 BEGIN
PRINT '
/****** Object: Index [IX_' + @CONSTRAINT_NAME + '] Script Date: ' + CONVERT(NVARCHAR(50), GetDate()) + ' ******/'
END
PRINT ' ALTER TABLE [dbo].[' + @TABLE_NAME + '] ADD CONSTRAINT [' + @CONSTRAINT_NAME + '] ' + @CREATE_AS + '
(
' +
SUBSTRING(@COLS, 1, LEN(@COLS)-1)
+ '

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
'
END
END
FETCH NEXT FROM FK_INDEX_CURSOR INTO @CONSTRAINT_NAME, @TABLE_SCHEMA, @TABLE_NAME
END

CLOSE FK_INDEX_CURSOR
DEALLOCATE FK_INDEX_CURSOR