Add Primary key

IF EXISTS (Select 1 from sysobjects where [name]='spAddPrimaryKey' and xtype='P')
Drop procedure [dbo].[spAddPrimaryKey]
GO


/*************************************************************************************************************************************

    Store Procedure Name :: spAddPrimaryKey
    Created By             :: Vinay Kumar

    Purpose                 ::    Add Primary key in those tables which doesn't contain primary key.
                         If table already contains any identity column with unique data then it'll converted in to primary key
                            OtherWise It'll add a new identity column with primary key constraint.
                            Primary key constraint = PK_+<Column_name>+<Table_Name>

    Input Parameters :: 5    
         @Print                 Type = Bit
                         1 for Print Statement 
                             0 for not Print Statement.
                             [Default Value :: 1]

         @Execute             Type = Bit 
                             1 for Execute Statement
                             0 for Not Execute Statement.
                             [Default Value :: 0]

         @Identitycheck Type = Bit 
                             check that Is any identity column contains duplicate value. 
                             [Default Value :: 0]

         @PrintStatement     Type = Bit 
                             This show resutl information that which tables are updated.
                             1 for print statements.
                             [Default Value :: 0]

         @IncludeTempTable     Type = Bit 
                             This Flag is used to include Temporary Tables.
                             1 for include Temporary tables.
                             [Default Value :: 0] 


    Output Parameters :: 0

*************************************************************************************************************************************/
Create procedure [dbo].[spAddPrimaryKey]
@Print bit = 1 
,@Execute bit = 0 
,@Identitycheck bit = 0 
,@PrintStatement bit = 0
,@IncludeTempTable bit = 0 
as 


SET NOCOUNT ON 
-- Create Tmp Table 
create table #temp11 (id int identity(1,1), tid int ,tname varchar(200),CoLName varchar(200),isidentity bit,IsTempTable bit) 
 
insert into #temp11 (tid,tname,ColName,isidentity,IsTempTable) 
select id,[name],bb.ColName,case when bb.tabid is null then 0 else 1 end 
,case when substring([name],0,4)='temp' then 1
when substring([name],len([name])-3,4)='temp' then 1
When substring([name],0,3)='temp' then 1
When substring([name],len([name])-2,3)='temp' then 1
else
0
end
from 
(select distinct id, [Name] from sysobjects where xtype='U' 
and id not in (select distinct parent_obj from sysobjects where xtype in ('PK','UQ') 
and parent_obj in (select id from sysobjects where xtype='U')) 
) aa 
left join 
(select c.object_id as [tabid],c.[Name] as ColName,s.[name] as tabName from sys.objects s inner join sys.columns c on s.object_id=c.object_id 
where type='U' and c.is_identity=1) bb on aa.id=bb.tabid --where tabid is null



Declare @Counter int 
Declare @Maxid int 
Declare @Tname varchar(200) 
Declare @Tcol varchar(100) 
Declare @Tid int 
Declare @ColName varchar(200) 
Declare @isidentity bit 
Declare @SqlQuery varchar(2000) 
declare @Result varchar(8000) 
Declare @IdentitycheckString varchar(8000)
Declare @IsTempTable bit
 
 
set @Counter=1
set @IsTempTable=0 
set @Result=' 
' 
set @IdentitycheckString='
---------- Show identity column information
'
select @Maxid=max(id) from #temp11 
 
set @SqlQuery =' 
/***************************************************************************************************************************** 
 If any table which doesn''t contain Primary key, This script add the primary key. 
 Note:- If table already contains any identity column and contain unique data then it''ll converted in to primary key 
*****************************************************************************************************************************/ 
 
 
' 
print(@SqlQuery) 

set @SQlQuery='
Select ''--------- Before Script ---------'' 
select distinct [Name] as ''Table which doesn''''t contain primary key'' from sysobjects where xtype=''U'' 
and id not in (select distinct parent_obj from sysobjects where xtype in (''PK'',''UQ'') 
and parent_obj in (select id from sysobjects where xtype=''U'')) order by 1'

Execute (@sqlQuery)


 
while (@counter<=@Maxid) 
begin 
 
 
select @Tid=Tid,@Tname=Tname,@ColName=ColName,@isidentity=isidentity ,@IsTempTable=IsTempTable from #temp11 where id=@counter 
select top 1 @Tcol= case when substring([Name],1,charindex('_',[Name]))='' then (Upper(substring(@Tname,1,3)))+'_' 
else substring([Name],1,charindex('_',[Name])) end from syscolumns where id=object_id(@Tname) 
 
set @Tcol=@Tcol+'PKkey'

if (@Identitycheck=1 and @isidentity=1)
begin
set @IdentitycheckString = @IdentitycheckString +'
select '''+@Tname+''' as ''Table_Name'', '''+@ColName+''' as ''Identity_Column_Name'', 1 as ''ISDuplicateValue'' from '+@Tname+' group by '+@ColName+' having count(1">'+@ColName+')>1
GO'
end
 

if (@isidentity=1) 
begin 
set @SQlQuery=' 
/********************************************************************* 
 Table Name :: '+@Tname+', Column Name :: '+@ColName+' 
*********************************************************************/ 
IF NOT EXISTS (select top 1 1 from '+@Tname+' group by '+@ColName+' having count(1">'+@ColName+')>1) 
begin 
IF NOT EXISTS(select 1 from sysobjects where sysobjects.[name]=''PK_'+@ColName+'_'+@Tname+''') 
begin 
 alter table ['+@Tname+'] add constraint [PK_'+@ColName+'_'+@Tname+'] primary key (['+@ColName+']) 
End 
End' 
set @Result=@Result+' 
Print ''Table :: ['+@Tname +'] Primary Column :: ['+@ColName+']'' ' 
end 
else 
begin 
 
set @SQlQuery=' 
/********************************************************************* 
 Table Name :: '+@Tname+', Column Name :: '+@Tcol+' 
*********************************************************************/ 
IF NOT EXISTS(select 1 from sysobjects where sysobjects.[name]=''PK_'+@Tcol+'_'+@Tname+''') 
begin 
Alter table ['+@Tname+'] add ['+@Tcol+'] int identity(1,1) constraint [PK_'+@Tcol+'_'+@Tname+'] primary key 
end' 
set @Result=@Result+' 
Print ''Table :: ['+@Tname +'] Primary Column :: ['+@Tcol+']'' ' 
end 
 

if (@Execute=1) 
begin 
    if (@IncludeTempTable=1)
        execute (@SQlQuery) 
    else
    begin
        if (@IsTempTable=0)
            execute (@SQlQuery) 
    end    

    
end 

if (@print=1) 
begin 
set @SQlQuery=@SQlQuery+' 
GO ' 
    if (@IncludeTempTable=1)
            print (@SQlQuery)
    else
    begin
        if (@IsTempTable=0)
            print (@SQlQuery)
    end

end 

set @SQlQuery=''


if (@IdentityCheck=1) 
    Print (@IdentityCheckString) 
 
if (@printstatement=1) 
print (@Result) 
 

set @SQlQuery=''
set @Result=' 
' 
set @IdentitycheckString='
'
set @counter=@counter+1 
 
end-- While end 
 


 
drop table #temp11 

 
set @SQlQuery='
Select ''--------- After Script ---------'' 
select distinct [Name] as ''Table which doesn''''t contain primary key'' from sysobjects where xtype=''U'' 
and id not in (select distinct parent_obj from sysobjects where xtype in (''PK'',''UQ'') 
and parent_obj in (select id from sysobjects where xtype=''U'')) order by 1'

Execute (@sqlQuery)

-------------------------- SP FINISHED



------ Execute statement

--EXECUTE spAddPrimaryKey 0,1,0,0,0