Return to Answer

improved formatting

Source Link

edit approved May 22, 2019 at 19:57

3.7k
14
48
79

declare @create_indexes nvarchar(max); set @create_indexes = N'';

declare @create_PK nvarchar(max); set @create_PK = N'';

select @create_indexes = @create_indexes + 'create clustered index ' + quotename('IX_' + object_name(i.object_id)) + char(13) + char(10) + 'on ' + quotename(object_name(i.object_id)) + '(' + quotename(c.name) + ');' + char(13) + char(10) + 'go' + char(13) + char(10) from sys.indexes i inner join sys.columns c on i.object_id = c.object_id where i.type = 0 and c.is_identity = 1;

select @create_PK = @create_PK + 'ALTER TABLE [dbo].' + quotename(object_name(i.object_id)) +' ADD CONSTRAINT ' + quotename('PK_' + object_name(i.object_id)) + ' PRIMARY KEY CLUSTERED ' + '(' + quotename(c.name) + ' ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];'
+ char(13) + char(10) + 'go' + char(13) + char(10) from sys.indexes i inner join sys.columns c on i.object_id = c.object_id where i.type = 0 and c.is_identity = 1;

--print @create_indexes;

print @create_PK;

declare @create_indexes nvarchar(max);
set @create_indexes = N'';

declare @create_PK nvarchar(max);
set @create_PK = N'';


select
    @create_indexes = @create_indexes + 
        'create clustered index ' + quotename('IX_' + object_name(i.object_id)) + 
        char(13) + char(10) +
        'on ' + quotename(object_name(i.object_id)) + '(' + quotename(c.name) + ');' +
        char(13) + char(10) + 
        'go' + 
        char(13) + char(10)
from sys.indexes i
inner join sys.columns c
on i.object_id = c.object_id
where i.type = 0
and c.is_identity = 1;

select
    @create_PK = @create_PK + 
        'ALTER TABLE [dbo].' + quotename(object_name(i.object_id)) +' ADD  CONSTRAINT '
         + quotename('PK_' + object_name(i.object_id)) + ' PRIMARY KEY CLUSTERED '
         + '(' + quotename(c.name) 
         + ' ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];'  
         + char(13) + char(10) + 
        'go' + 
        char(13) + char(10)
from sys.indexes i
inner join sys.columns c
on i.object_id = c.object_id
where i.type = 0
and c.is_identity = 1;


--print @create_indexes;

print @create_PK;

declare @create_indexes nvarchar(max); set @create_indexes = N'';

declare @create_PK nvarchar(max); set @create_PK = N'';

--print @create_indexes;

print @create_PK;

declare @create_indexes nvarchar(max);
set @create_indexes = N'';

declare @create_PK nvarchar(max);
set @create_PK = N'';


select
    @create_indexes = @create_indexes + 
        'create clustered index ' + quotename('IX_' + object_name(i.object_id)) + 
        char(13) + char(10) +
        'on ' + quotename(object_name(i.object_id)) + '(' + quotename(c.name) + ');' +
        char(13) + char(10) + 
        'go' + 
        char(13) + char(10)
from sys.indexes i
inner join sys.columns c
on i.object_id = c.object_id
where i.type = 0
and c.is_identity = 1;

select
    @create_PK = @create_PK + 
        'ALTER TABLE [dbo].' + quotename(object_name(i.object_id)) +' ADD  CONSTRAINT '
         + quotename('PK_' + object_name(i.object_id)) + ' PRIMARY KEY CLUSTERED '
         + '(' + quotename(c.name) 
         + ' ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];'  
         + char(13) + char(10) + 
        'go' + 
        char(13) + char(10)
from sys.indexes i
inner join sys.columns c
on i.object_id = c.object_id
where i.type = 0
and c.is_identity = 1;


--print @create_indexes;

print @create_PK;

Source Link

created May 22, 2019 at 18:33

Tony Lucero

declare @create_indexes nvarchar(max); set @create_indexes = N'';

declare @create_PK nvarchar(max); set @create_PK = N'';

--print @create_indexes;

print @create_PK;