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

Advertisements

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