I have written a query that partitions the tables of a database with a certain criteria.
Tables with more than 50 records.
-------------------------------------
USE [Post]
SET NOCOUNT ON;
BEGIN TRANSACTION
begin try
CREATE PARTITION FUNCTION [PF](datetime2(7)) AS RANGE LEFT FOR VALUES (N'2023-03-20', N'2023-09-22', N'2024-03-19', N'2024-09-21', N'2025-03-20', N'2025-09-22', N'2026-03-20')
CREATE PARTITION SCHEME [PS] AS PARTITION [PF] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],[PRIMARY], [PRIMARY], [PRIMARY],[PRIMARY])
--delete[] all Table fk dependency
Declare
@Frank_keough_Name nvarchar(100),@T_Name nvarchar(100),@S_Name nvarchar(100);
Declare
@RT_Name nvarchar(100),@RS_Name nvarchar(100);
Declare @DatabaseName nvarchar(100);
Declare @SQL nvarchar(2000);
Declare @SQL1 nvarchar(2000);
DECLARE @tab AS TABLE (col1 nVARCHAR(500), col2 nvarchar(500),col3 nvarchar(500),col4 nvarchar(500),col5 nvarchar(500),col6 nvarchar(500));
--DECLARE @tab3 AS TABLE (col11 nVARCHAR(100), col12 nvarchar(100),col13 nvarchar(100),col14 nVARCHAR(100), col15 nvarchar(100));
DEclare @column1 nvarchar(100);
Declare @Str nVARCHAR(100);
Declare @count as int;
Declare @count_11 as int;
DECLARE @retval nvarchar(100) ;
----------------------------------Count of Table Candidatefor Partitioning----------------------
Declare @Count_Table as int;
set @Count_Table=(select count (*) from
(SELECT distinct
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [Table For Partition Candidate]
, (sPTN.Rows) as pp
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
and SCHEMA_NAME(sOBJ.schema_id)<>'basicinfo' and
sOBJ.name not in
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME not in
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%creationdatetime%'))
)as t where t.pp>50)
----------------------------------Start Of Loop-----------------------
if ( @Count_Table>0)
begin
Declare cr1 scroll Cursor
for
select distinct [Table_For_Partition_Candidate] from
(SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [Table_For_Partition_Candidate]
, (sPTN.Rows) as pp
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
and SCHEMA_NAME(sOBJ.schema_id)<>'basicinfo' and
sOBJ.name not in
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME not in
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%creationdatetime%'))
)as t where t.pp>50
Open cr1
fetch next from cr1 into @DatabaseName;
while( @Count_Table>0)
begin
print '------------------------------------'+@DatabaseName+'------------------------------------'
set @SQL= 'SELECT distinct fk.name,OBJECT_Schema_NAME(fk.parent_object_id),OBJECT_NAME(fk.parent_object_id),
OBJECT_Schema_NAME(fk.referenced_object_id),OBJECT_NAME(fk.referenced_object_id),
c_parent.name FROM sys.foreign_keys fk inner join sys.foreign_key_columns fkc
on fkc.constraint_object_id = fk.object_id iNNER JOIN sys.tables t_parent ON t_parent.object_id = fk.parent_object_id
INNER JOIN sys.columns c_parent ON fkc.parent_column_id = c_parent.column_id AND c_parent.object_id = t_parent.object_id
INNER JOIN sys.tables t_child ON t_child.object_id = fk.referenced_object_id INNER JOIN sys.columns c_child
ON c_child.object_id = t_child.object_id
WHERE fk.referenced_object_id = OBJECT_ID( '''+@DatabaseName+''')';
INSERT into @tab EXEC sp_executesql @SQL;
------------------
set @Count_11=(select count (*) from @tab);
Declare crqq scroll Cursor
for
select * from @tab
open crqq
while(@Count_11>0)
begin
fetch next from crqq into
@Frank_keough_Name ,@S_Name,@T_Name,
@RT_Name ,@RS_Name ,@column1 ;
-- select * from @tab
begin try
Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+'] DROP CONSTRAINT ['+@FK_Name+']');
-- IF NOT EXISTS(SELECT * FROM sys.indexes WHERE Name = '+@FK_Name+')
SET @SQL='ALTER TABLE ['+@S_Name+'].['+@T_Name+'] DROP CONSTRAINT ['+@FK_Name+']';
EXEC sp_executesql @SQL
end try
begin catch
Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+'] DROP CONSTRAINT ['+@FK_Name+']-----Faild');
end catch
set @count_11-=1;
end
Close crqq
Deallocate crqq
set @retval=(SELECT top(1) name FROM sys.indexes WHERE object_id = OBJECT_ID( ''+@DatabaseName+'') AND index_id = 1 AND is_primary_key = 1)
if @retval is not Null
begin
--print @sql
begin try
set @sql='ALTER TABLE '+ @DatabaseName +' DROP CONSTRAINT ['+@retval+'] WITH ( ONLINE = OFF )'
EXEC sp_executesql @SQL
print @sql
end try
begin catch
set @sql='ALTER TABLE '+ @DatabaseName +' DROP CONSTRAINT ['+@retval+'] WITH ( ONLINE = OFF )------Failed'
print @sql
end catch
begin try
set @SQL='ALTER TABLE '+ @DatabaseName +' ADD CONSTRAINT['+@retval+'] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]'
print @sql
EXEC sp_executesql @SQL
end try
Begin catch
set @SQL='ALTER TABLE '+ @DatabaseName +' ADD CONSTRAINT['+@retval+'] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]-----Failed'
print @sql
end catch
end
if @retval is NUll
set @Retval='EMPTY';
begin try
set @SQL='CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_'+@retval+ '] ON ' + @DatabaseName +'( [CreationDateTime] ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS]([CreationDateTime])'
EXEC sp_executesql @SQL
print @sql
set @SQL='DROP INDEX [ClusteredIndex_on_PS_'+@retval+'] ON ' + @DatabaseName
EXEC sp_executesql @SQL
print @sql
--set @SQL='CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_'+@retval+ '] ON ' + @DatabaseName +'( [CreationDateTime],[ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS]([CreationDateTime])'
--EXEC sp_executesql @SQL
-- print @sql
end try
begin catch
set @SQL='CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_'+@retval+'] ON '+@DatabaseName+'
(
[CreationDateTime]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS]([CreationDateTime])-----Faild or'+'CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_'+@retval+ '] ON ' + @DatabaseName +'( [ID] ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF) ON [PS]([CreationDateTime])-------faild'
print @sql
end catch
Declare p scroll Cursor
for(select * from @tab)
set @count=(select count(col1) from @tab);
Open p
while(@count>0)
begin
fetch next from p into @Frank_keough_Name ,@S_Name,@T_Name, @RT_Name ,@RS_Name ,@column1 ; begin try
SET @SQL='ALTER TABLE ['+@S_Name+'].['+@T_Name+'] WITH CHECK ADD CONSTRAINT ['+@FK_Name+'] FOREIGN KEY(['+@column1+']) REFERENCES '+@DatabaseName+'([ID])';
Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+'] WITH CHECK ADD CONSTRAINT ['+@FK_Name+'] FOREIGN KEY(['+@column1+']) REFERENCES '+@DatabaseName+'([ID])');
EXEC sp_executesql @SQL
SET @SQL='ALTER TABLE ['+@S_Name+'].['+@T_Name+'] CHECK CONSTRAINT ['+@FK_Name+']';
Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+'] CHECK CONSTRAINT ['+@FK_Name+']');
EXEC sp_executesql @SQL
end try
begin catch
Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+'] WITH CHECK ADD CONSTRAINT ['+@FK_Name+'] FOREIGN KEY(['+@column1+']) REFERENCES '+@DatabaseName+'([ID])----Faild');
Print('ALTER TABLE ['+@S_Name+'].['+@T_Name+'] CHECK CONSTRAINT ['+@FK_Name+']---Faild');
end catch
set @count-=1;
end
Close p
Deallocate p
DELETE FROM @tab where 1=1;
-----------------------End of Loop------------------------
fetch next from cr1 into @DatabaseName;
set @Count_Table-=1;
end
Close cr1
Deallocate cr1
end--if
end try
begin catch
print 'All Operation Faild Because Dont Create PF and PS'
end catch
COMMIT TRANSACTION
