eg.sql

exec MkPrimaryKey 'actcode', 'acode', @display = 1, @exec = 0
exec MkPrimaryKey 'csd', 'baid,cknum', @display = 1, @exec = 0

MkPrimaryKey.sql

if object_id('MkPrimaryKey') is not null
	drop proc MkPrimaryKey
go
create proc MkPrimaryKey 
	@TblName sysname
,	@ColNames varchar(max)
,	@exec bit = 1
,	@display bit = 0
as
	set nocount on
	declare @sql varchar(max)
	,		@tid int
	,		@SchName sysname
	,		@TblFullNm varchar(max)
	
	select	@tid = object_id
	,		@SchName = schema_name(schema_id)
	from	sys.tables
	where	name = @TblName
	
	select	@TblFullNm = quotename(@SchName) + '.' + quotename(@TblName)
	
	if @tid is null
		begin
		print quotename(@TblName) + ' does not exist'
		return
		end

	if 1 = (
		select	1
		from	sys.indexes
		where	object_id = @tid
		and		is_primary_key = 1
		)
		begin
		print @TblFullNm + ' already has primary key'
		if @exec = 1 return
		end

	declare	@cols table (Name sysname)
	insert	@cols
	select	s
	from	dbo.Split(@ColNames)
	
	if	(
		select	count(*)
		from	sys.columns c
		join	@cols t on t.Name = c.Name
		where	object_id = @tid
		) < (
		select	count(*)
		from	@cols
		)
		begin
		print @TblFullNm + ': not all keys exist'
		return
		end
		
	-- make sure target columns are not nullable

	set	@sql = null
	select	@sql = isnull(@sql + char(10), '')
	+		'if ('
	+			'select is_nullable '
	+			'from sys.columns '
	+			'where object_id = ' + cast(@tid as varchar(max)) + ' '
	+			'and name = ' + quotename(c.Name, char(39))
	+			') = 1 '
	+		'alter table ' + @TblFullNm + ' '
	+		'alter column ' + quotename(c.Name) + ' '
	+		c.Datatype + ' ' + c.Collation + ' '
	+		'not null'
	from	TabCols(@TblName) c
	join	@cols n	on n.Name = c.Name
			
	if @display = 1 print @sql
	if @exec = 1 exec(@sql)
	
	select @sql = '
		alter table %tblname%
		add constraint [pk-%ctname%] 
		primary key clustered (%colnames%)
	'
	set @ColNames = null
	select	@ColNames = isnull(@ColNames + ',', '')
	+		quotename(Name)
	from	@cols
	
	select @sql = replace(@sql, '%tblname%', @TblFullNm)
	select @sql = replace(@sql, '%colnames%', @ColNames)
	select @sql = replace(@sql, '%ctname%', @SchName + '.' + @Tblname)
	if @display = 1 print @sql
	if @exec = 1 exec(@sql)
	print @TblFullNm + ' primary key created'
go