Useful Queries for Development

Alter all Columns in all Tables in the Database to allow NULLs

-- Alters all columns in all Tables in the database to allow NULLs (Excludes all Primary Keys)
DECLARE @sqlaltertable NVARCHAR(MAX) = N''
DECLARE @i int = 1
DECLARE @count int
DECLARE @objectid int
DECLARE @object NVARCHAR(500) = N''
--Create Source List temp table
CREATE TABLE #SourceList (
[Id] [int] IDENTITY(1,1) NOT NULL,
[ObjectId] [int] NOT NULL
)
--insert into table #SourceList
INSERT INTO #SourceList
SELECT 
distinct tb.[OBJECT_ID]
FROM SYS.COLUMNS C 
INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id]
INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id 
WHERE 
C.is_nullable = 0
AND C.is_identity = 0
AND tb.[type] = 'U'

SET @count = @@ROWCOUNT

--Create index for the temp table
CREATE CLUSTERED INDEX #SourceList0
ON #SourceList ([Id],[ObjectId])

WHILE (@i <= @count)
BEGIN

SET @objectid = (SELECT [ObjectId] FROM #SourceList WHERE [Id] = @i)

SET @object = (SELECT OBJECT_NAME (@objectid))

SELECT @sqlaltertable = @sqlaltertable +[sqlaltertable] FROM
(
SELECT [sqlaltertable] = N'
ALTER TABLE '
+ QUOTENAME(SCHEMA_NAME(tb.[schema_id])) + '.' + QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID]))
+ ' ALTER COLUMN ' + C.NAME + ' ' +
CASE WHEN T.name IN ('bigint','bit','datetime','float','image','int','money','smalldatetime','smallint','text','tinyint','uniqueidentifier','xml') THEN T.name 
WHEN T.name IN ('char','nchar') THEN T.name + '(' + convert(varchar(5),C.max_length) + ')'
WHEN T.name IN ('numeric') THEN T.name + '(' + convert(varchar(5),C.precision) + ',' + convert(varchar(5),C.scale)+ ')'
WHEN T.name IN ('nvarchar','varchar','varbinary') AND C.max_length = -1 THEN T.name + '(max)'
WHEN T.name IN ('nvarchar','varchar') AND C.max_length <> -1 THEN T.name + '(' + convert(varchar(5),C.max_length) + ')'
WHEN T.name IN ('decimal') THEN '[dbo].[EXPCURRENCY]' 
WHEN T.name IN ('real') THEN '[dbo].[frlCurrencyRate]'
END + ' NULL;'
FROM SYS.COLUMNS C 
INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id]
INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id 
WHERE 
C.is_nullable = 0
AND C.is_identity = 0
and tb.[type] = 'U'
and T.name IN ('bigint','bit','datetime','float','image','int','money','smalldatetime','smallint','text','tinyint','uniqueidentifier','xml','char','nchar','numeric','nvarchar','varchar','varbinary','decimal','real')
and tb.object_id = @objectid
and not exists (

SELECT object_id(CS.TABLE_SCHEMA + '.' + CS.TABLE_NAME) , k.COLUMN_NAME
FROM information_schema.table_constraints AS CS 
INNER JOIN information_schema.key_column_usage AS K
ON CS.table_name = K.table_name 
AND CS.constraint_catalog = 
K.constraint_catalog 
AND CS.constraint_schema = 
K.constraint_schema 
AND CS.constraint_name = 
K.constraint_name 
WHERE CS.constraint_type = 'PRIMARY KEY' 
and object_id(CS.TABLE_SCHEMA + '.' + CS.TABLE_NAME) = tb.[OBJECT_ID]
and C.NAME = k.COLUMN_NAME)
) AS RA;

BEGIN
BEGIN TRY
EXECUTE sp_executesql @sqlaltertable
PRINT '['+@object+'] altered successfully'
PRINT ''
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000); 
DECLARE @ErrorSeverity INT; 
DECLARE @ErrorState INT;

SELECT 
@ErrorMessage = ERROR_MESSAGE(), 
@ErrorSeverity = ERROR_SEVERITY(), 
@ErrorState = ERROR_STATE();

PRINT '***PROBLEM ALTERING TABLE***'
PRINT ''
RAISERROR (@ErrorMessage, -- Message text. 
@ErrorSeverity, -- Severity. 
@ErrorState -- State. 
); 
END CATCH
END

SET @sqlaltertable = N''

SET @i = @i +1

END

Migrate Specific Tables – To Specified File Group

/*
5. Migrate TAT_TIME and CDT_DISB Tables (Specific Tables) 
Move data and indexes for all the TAT_TIME* and all the CDT_DISB* tables to the specified file group.
Please take the latest backup and test this script before executing on the production database.
Please check the values for the variables below. And first execute it in Debug Mode for quick testing.
@NewFilegroup Name of the filegroup to which you'll be moving the clustered indexes.
@Debug When set to "1", generates a script without actually moving anything. This can be used to preview what
will be done. Setting this to "0" will cause this script to actually migrate the indexes. 
*/

DECLARE @NewFilegroup nvarchar(100) = 'AdwentureWorksTimeDisb'
DECLARE @Debug BIT = 0

DECLARE @id integer
DECLARE @tbname nvarchar(100)
DECLARE @indid integer
DECLARE @indname nvarchar(100)
DECLARE @fill integer
DECLARE @unique VARCHAR(20)
DECLARE @clustered VARCHAR(20)
DECLARE @schema VARCHAR(20)
DECLARE @column_list nvarchar(4000)
DECLARE @include_list nvarchar(4000)
DECLARE @strsql nvarchar(4000)
DECLARE @crlf CHAR(2) = CHAR(13)+CHAR(10)

IF @Debug = 1
PRINT 'Executing in Debug Mode Only.........' + @crlf + @crlf

IF NOT EXISTS (SELECT 1 FROM sys.filegroups where name=@NewFilegroup) BEGIN
RAISERROR('Filegroup [%s] does not exist. Aborting.', 10,1, @NewFilegroup)
RETURN
END

declare curs1 cursor for
SELECT 
t.object_id,
t.name, 
i.index_id,
i.name AS indname,
CASE i.Fill_Factor WHEN 0 THEN 90 ELSE i.Fill_Factor END AS Fill_Factor,
CASE i.Is_Unique WHEN 0 THEN '' WHEN 1 THEN ' UNIQUE ' END AS Is_Unique,
CASE i.type_desc WHEN 'NONCLUSTERED' THEN '' WHEN 'CLUSTERED' THEN 'CLUSTERED' END AS Is_Clustered,
s.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS i
ON t.object_id = i.object_id
WHERE t.type = 'U'
AND (i.type_desc = 'NONCLUSTERED' OR i.type_desc = 'CLUSTERED')
AND i.is_disabled = 0
AND (t.name LIKE 'TAT_TIME%' OR t.name LIKE 'CDT_DISB%') --Specific Tables
ORDER BY t.name, i.name

open curs1
fetch next from curs1 into @id,@tbname,@indid,@indname,@fill,@unique,@clustered,@schema


while @@fetch_status=0
begin

SET @Column_List=''
SET @Include_List = ''

SELECT @Column_List = @Column_List + CASE is_included_column WHEN 0 THEN '['+ c.name+'],' ELSE '' END,
@Include_List = @Include_List + CASE is_included_column WHEN 1 THEN '['+ c.name+'],' ELSE '' END
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
JOIN sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE o.type = 'U'
AND i.index_id = @indid
AND o.object_id = @id
ORDER BY ic.key_ordinal

SET @Column_List=left(@Column_List,len(@Column_List)-1)

IF LEN(@Include_List) > 0
BEGIN
SET @Include_List = left(@Include_List,len(@Include_List)-1)
SET @Include_List = ' INCLUDE ('+@Include_List+') '
END

begin
set @strsql = 'CREATE '+@Unique+ @clustered+' INDEX ['+@indname+'] ON ['+@schema+'].['+@tbname+']('+@Column_List+') '+@Include_List+' WITH (DROP_EXISTING=ON,FILLFACTOR = '+CONVERT(VARCHAR(5), @fill)+') ON ['+@NewFilegroup+']'

IF @Debug=1
BEGIN
print @strsql
print ''
END

IF @Debug=0
BEGIN
BEGIN TRY
exec sp_executesql @strsql
PRINT '['+@tbname+'].['+@indname+'] moved successfully'
PRINT ''
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000); 
DECLARE @ErrorSeverity INT; 
DECLARE @ErrorState INT;

SELECT 
@ErrorMessage = ERROR_MESSAGE(), 
@ErrorSeverity = ERROR_SEVERITY(), 
@ErrorState = ERROR_STATE();

PRINT '***PROBLEM MIGRATING THIS INDEX***'
PRINT ''
RAISERROR (@ErrorMessage, -- Message text. 
@ErrorSeverity, -- Severity. 
@ErrorState -- State. 
); 
END CATCH
END

end
fetch next from curs1 into @id,@tbname,@indid,@indname,@fill,@unique,@clustered,@schema
end
close curs1
deallocate curs1

Useful Queries for Development

Migrate Non Clustered Indexes – To Specified File Group

/*
4. Migrate Non Clustered Indexes
Move non clustered indexes to the specified file group.
Please take the latest backup and test this script before executing on the production database.
Please check the values for the variables below. And first execute it in Debug Mode for quick testing.
@NewFilegroup Name of the filegroup to which you'll be moving the clustered indexes.
@Debug When set to "1", generates a script without actually moving anything. This can be used to preview what
will be done. Setting this to "0" will cause this script to actually migrate the indexes. 
*/

DECLARE @NewFilegroup nvarchar(100) = 'AdwentureWorksNonClustered'
DECLARE @Debug BIT = 0

DECLARE @id integer
DECLARE @tbname nvarchar(100)
DECLARE @indid integer
DECLARE @indname nvarchar(100)
DECLARE @fill integer
DECLARE @unique VARCHAR(20)
DECLARE @schema VARCHAR(20)
DECLARE @column_list nvarchar(4000)
DECLARE @include_list nvarchar(4000)
DECLARE @strsql nvarchar(4000)
DECLARE @crlf CHAR(2) = CHAR(13)+CHAR(10)

IF @Debug = 1
PRINT 'Executing in Debug Mode Only.........' + @crlf + @crlf

IF NOT EXISTS (SELECT 1 FROM sys.filegroups where name=@NewFilegroup) BEGIN
RAISERROR('Filegroup [%s] does not exist. Aborting.', 10,1, @NewFilegroup)
RETURN
END

declare curs1 cursor for
SELECT 
t.object_id,
t.name, 
i.index_id,
i.name AS indname,
CASE i.Fill_Factor WHEN 0 THEN 90 ELSE i.Fill_Factor END AS Fill_Factor,
CASE i.Is_Unique WHEN 0 THEN '' WHEN 1 THEN ' UNIQUE ' END AS Is_Unique,
s.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS i
ON t.object_id = i.object_id
WHERE t.type = 'U'
AND i.type_desc = 'NONCLUSTERED'
AND i.is_disabled = 0
AND t.name NOT LIKE 'TAT_TIME%'
AND t.name NOT LIKE 'CDT_DISB%'
ORDER BY t.name, i.name

open curs1
fetch next from curs1 into @id,@tbname,@indid,@indname,@fill,@unique,@schema


while @@fetch_status=0
begin

SET @Column_List=''
SET @Include_List = ''

SELECT @Column_List = @Column_List + CASE is_included_column WHEN 0 THEN '['+ c.name+'],' ELSE '' END,
@Include_List = @Include_List + CASE is_included_column WHEN 1 THEN '['+ c.name+'],' ELSE '' END
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
JOIN sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE o.type = 'U'
AND i.index_id = @indid
AND o.object_id = @id
ORDER BY ic.key_ordinal

SET @Column_List=left(@Column_List,len(@Column_List)-1)

IF LEN(@Include_List) > 0
BEGIN
SET @Include_List = left(@Include_List,len(@Include_List)-1)
SET @Include_List = ' INCLUDE ('+@Include_List+') '
END

begin
set @strsql = 'CREATE '+@Unique+' INDEX ['+@indname+'] ON ['+@schema+'].['+@tbname+']('+@Column_List+') '+@Include_List+' WITH (DROP_EXISTING=ON,FILLFACTOR = '+CONVERT(VARCHAR(5), @fill)+') ON ['+@NewFilegroup+']'

IF @Debug=1
BEGIN
print @strsql
print ''
END

IF @Debug=0
BEGIN
BEGIN TRY
exec sp_executesql @strsql
PRINT '['+@tbname+'].['+@indname+'] moved successfully'
PRINT ''
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000); 
DECLARE @ErrorSeverity INT; 
DECLARE @ErrorState INT;

SELECT 
@ErrorMessage = ERROR_MESSAGE(), 
@ErrorSeverity = ERROR_SEVERITY(), 
@ErrorState = ERROR_STATE();

PRINT '***PROBLEM MIGRATING THIS INDEX***'
PRINT ''
RAISERROR (@ErrorMessage, -- Message text. 
@ErrorSeverity, -- Severity. 
@ErrorState -- State. 
); 
END CATCH
END

end
fetch next from curs1 into @id,@tbname,@indid,@indname,@fill,@unique,@schema
end
close curs1
deallocate curs1

Useful Queries for Development

Migrate Heap Tables – To Specified File Group

/*
3. Migrate Heap Tables
Move all heap tables to the specified file group
Please take the latest backup and test this script before executing on the production database.
Please check the values for the variables below. And first execute it in Debug Mode for quick testing.
@NewFilegroup Name of the filegroup to which you'll be moving the clustered indexes.
@Debug When set to "1", generates a script without actually moving anything. This can be used to preview what
will be done. Setting this to "0" will cause this script to actually migrate the indexes. 
*/

DECLARE @NewFilegroup nvarchar(100) = 'AdwentureWorksClustered'
DECLARE @Debug BIT = 0

DECLARE @id integer
DECLARE @tbname nvarchar(100)
DECLARE @fill integer
DECLARE @colname VARCHAR(20)
DECLARE @schema VARCHAR(20)
DECLARE @column_list nvarchar(4000)
DECLARE @include_list nvarchar(4000)
declare @strsql_ac nvarchar(4000)
declare @strsql_ai nvarchar(4000)
declare @strsql_m nvarchar(4000)
declare @strsql_di nvarchar(4000)
declare @strsql_dc nvarchar(4000)
DECLARE @crlf CHAR(2) = CHAR(13)+CHAR(10)
DECLARE @Filegroup nvarchar(100) = 'PRIMARY'

IF @Debug = 1
PRINT 'Executing in Debug Mode Only.........' + @crlf + @crlf

IF NOT EXISTS (SELECT 1 FROM sys.filegroups where name=@NewFilegroup) BEGIN
RAISERROR('Filegroup [%s] does not exist. Aborting.', 10,1, @NewFilegroup)
RETURN
END

SELECT DISTINCT object_id(c.TABLE_SCHEMA+'.'+c.TABLE_NAME) AS ObjectId, c.COLUMN_NAME AS colname
INTO #Heapidentity
FROM INFORMATION_SCHEMA.COLUMNS As c
WHERE COLUMNPROPERTY(object_id(c.TABLE_SCHEMA+'.'+c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') = 1

declare curs1 cursor for
SELECT
t.object_id,
t.name,
90 AS Fill_Factor,
s.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS i
ON t.object_id = i.object_id
LEFT JOIN #Heapidentity As h
ON t.object_id = h.ObjectId
WHERE t.type = 'U'
AND i.type_desc = 'HEAP'
AND i.is_disabled = 0
AND h.ObjectId IS NULL
ORDER BY t.name

open curs1
fetch next from curs1 into @id,@tbname,@fill,@schema


while @@fetch_status=0
begin
SET @Column_List='Id_heap_new'
SET @Include_List = ''

set @strsql_ac = 'ALTER TABLE ['+@schema+'].['+@tbname+'] ADD Id_heap_new Int Identity(1, 1)'
set @strsql_ai = 'CREATE UNIQUE CLUSTERED INDEX ['+@tbname+'HeapMigrate] ON ['+@schema+'].['+@tbname+']('+@Column_List+') '+@Include_List+' WITH FILLFACTOR = '+CONVERT(VARCHAR(5), @fill)+' ON ['+@Filegroup+']'
set @strsql_m = 'CREATE UNIQUE CLUSTERED INDEX ['+@tbname+'HeapMigrate] ON ['+@schema+'].['+@tbname+']('+@Column_List+') '+@Include_List+' WITH (DROP_EXISTING=ON,FILLFACTOR = '+CONVERT(VARCHAR(5), @fill)+') ON ['+@NewFilegroup+']'
set @strsql_di = 'DROP INDEX ['+@tbname+'HeapMigrate] ON ['+@schema+'].['+@tbname+']'
set @strsql_dc = 'ALTER TABLE ['+@schema+'].['+@tbname+'] DROP COLUMN Id_heap_new'

IF @Debug=1
BEGIN
print @strsql_ac
print @strsql_ai
print @strsql_m
print @strsql_di
print @strsql_dc
print ''
END

IF @Debug=0
BEGIN
BEGIN TRY
exec sp_executesql @strsql_ac
exec sp_executesql @strsql_ai
exec sp_executesql @strsql_m
exec sp_executesql @strsql_di
exec sp_executesql @strsql_dc
PRINT '['+@tbname+'] moved successfully'
PRINT ''
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000); 
DECLARE @ErrorSeverity INT; 
DECLARE @ErrorState INT;

SELECT 
@ErrorMessage = ERROR_MESSAGE(), 
@ErrorSeverity = ERROR_SEVERITY(), 
@ErrorState = ERROR_STATE();

PRINT '***PROBLEM MIGRATING THIS INDEX***'
PRINT ''
RAISERROR (@ErrorMessage, -- Message text. 
@ErrorSeverity, -- Severity. 
@ErrorState -- State. 
); 
END CATCH
END

fetch next from curs1 into @id,@tbname,@fill,@schema
end
close curs1
deallocate curs1

---------------------------------------------------------------------
declare curs2 cursor for
SELECT
t.object_id,
t.name,
90 AS Fill_Factor,
s.name,
h.colname
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS i
ON t.object_id = i.object_id
INNER JOIN #Heapidentity As h
ON t.object_id = h.ObjectId
WHERE t.type = 'U'
AND i.type_desc = 'HEAP'
AND i.is_disabled = 0
ORDER BY t.name

open curs2
fetch next from curs2 into @id,@tbname,@fill,@schema,@colname


while @@fetch_status=0
begin

SET @Include_List = ''

set @strsql_ai = 'CREATE UNIQUE CLUSTERED INDEX ['+@tbname+'HeapMigrate] ON ['+@schema+'].['+@tbname+']('+@colname+') '+@Include_List+' WITH FILLFACTOR = '+CONVERT(VARCHAR(5), @fill)+' ON ['+@Filegroup+']'
set @strsql_m = 'CREATE UNIQUE CLUSTERED INDEX ['+@tbname+'HeapMigrate] ON ['+@schema+'].['+@tbname+']('+@colname+') '+@Include_List+' WITH (DROP_EXISTING=ON,FILLFACTOR = '+CONVERT(VARCHAR(5), @fill)+') ON ['+@NewFilegroup+']'
set @strsql_di = 'DROP INDEX ['+@tbname+'HeapMigrate] ON ['+@schema+'].['+@tbname+']'

IF @Debug=1
BEGIN
print @strsql_ai
print @strsql_m
print @strsql_di
print ''
END

IF @Debug=0
BEGIN
BEGIN TRY
exec sp_executesql @strsql_ai
exec sp_executesql @strsql_m
exec sp_executesql @strsql_di
PRINT '['+@tbname+'] moved successfully'
PRINT ''
END TRY
BEGIN CATCH
SELECT 
@ErrorMessage = ERROR_MESSAGE(), 
@ErrorSeverity = ERROR_SEVERITY(), 
@ErrorState = ERROR_STATE();

PRINT '***PROBLEM MIGRATING THIS INDEX***'
PRINT ''
RAISERROR (@ErrorMessage, -- Message text. 
@ErrorSeverity, -- Severity. 
@ErrorState -- State. 
); 
END CATCH
END

fetch next from curs2 into @id,@tbname,@fill,@schema,@colname
end
close curs2
deallocate curs2

M	T	W	T	F	S	S
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31