ALTER proc [archiv].[prc_table_body_difinition]
@db varchar(255),
@schema varchar(255),
@table varchar(255),
@difinition varchar(max) out

as

/* test
declare @difinition varchar(max) 
exec archiv.prc_table_body_difinition 'dwh', 'dbo', 'dim_card', @difinition out
print @difinition
*/

set nocount on
select @difinition=''

declare @table_full_name varchar(2000) = '['+@db+'].['+@schema+'].['+@table+']' ,
	@com varchar(max) = ''
	
if (select object_id(@table_full_name)) is not null
begin

	select @com=
		'
	declare @columns varchar(max) = ''
(''		
		, @difinition varchar(max) = ''create table ''+'''+@table_full_name+'''

	select @columns=@columns+c.name+'' ''+t.name
		+case 
			when c.collation_name is not null then ''(''+cast(c.max_length as varchar)+'')''
			when c.collation_name is null and t.scale=0 then ''''
			when c.collation_name is null and t.scale>0 then ''(''+cast(c.precision as varchar)+'', ''+cast(c.scale as varchar)+'')''
			else cast(c.precision as varchar) end
		+case when c.is_nullable=0 then '' not null'' else '' null'' end
		+'', 
''
	from ['+@db+'].[sys].[columns] c
	join ['+@db+'].[sys].types t on t.system_type_id=c.system_type_id
	where c.[object_id]=object_id('''+@table_full_name+''')
		and c.is_computed=0
	order by c.column_id

	select @columns = left(@columns, len(@columns)-4)+'')''

	select @difinition=@difinition + @columns +'';''	
	select @difinition'

	exec dbo.prc_variable_from_exec @com, @difinition out

end

ALTER proc [archiv].[prc_table_index_difinition]
@db varchar(255),
@schema varchar(255),
@table varchar(255),
@key_only bit = 0,
@cluster_only bit = 0,
@difinition varchar(max) out

as

/* test
declare @difinition varchar(max) 
exec archiv.prc_table_index_difinition 'dwh', 'dbo', 'dim_product', @difinition out
print @difinition
*/

set nocount on
select @difinition=''
declare @table_full_name varchar(2000) = '['+@db+'].['+@schema+'].['+@table+']' ,
	@com varchar(max) = ''
	
if (select object_id(@table_full_name)) is not null
begin

	select @com=
	'
	declare @difinition varchar(max) = ''''

	select identity(int, 1, 1) as id, 
		i.name, i.type, i.is_unique, i.is_primary_key, i.fill_factor,
		tc.name column_name, c.index_column_id, c.key_ordinal, c.is_descending_key, c.is_included_column
	into #dif
	from ['+@db+'].[sys].[indexes] i
	join ['+@db+'].[sys].[index_columns] c 
		on c.[object_id]=i.[object_id]
		and c.[index_id]=i.[index_id]
	join ['+@db+'].[sys].[columns] tc
		on tc.object_id=i.object_id
		and c.column_id=tc.column_id
	where i.[object_id]=object_id('''+@table_full_name+''')
		and (i.type_desc=''CLUSTERED''
			or c.partition_ordinal=0)
	order by i.index_id, c.index_column_id

	create table #columns
	(name varchar(255),
	columns varchar(max))

	create table #columns_included
	(name varchar(255),
	columns varchar(max))

	declare @i int = 1

	while @i<=(select max(id) from #dif)
	begin

		-- columns
		if (select name from #columns where name=(select name from #dif where id=@i)) is null
		insert into #columns
		select name, ''(''+column_name+'',''
		from #dif
		where id=@i
		else 
		update c
		set c.columns=columns+d.column_name
			+case when d.index_column_id=1 then '' desc '' else '''' end
			+'',''
		from #dif d
		join #columns c on c.name=d.name
		where id=@i 
			and d.is_included_column=0

		-- columns included
		if (select name from #columns_included where name=(select name from #dif where id=@i)) is null
		insert into #columns_included
		select name, ''(''+column_name+'',''
		from #dif d
		where id=@i
			and d.is_included_column=1
		else 
		update c
		set c.columns=columns+d.column_name
			+case when d.index_column_id=1 then '' desc '' else '''' end
			+'',''
		from #dif d
		join #columns_included c on c.name=d.name
		where id=@i 
			and d.is_included_column=1

		set @i=@i+1

	end

	update #columns
	set columns=left(columns, len(columns)-1)+'')''

	update #columns_included
	set columns=left(columns, len(columns)-1)+'')''


	select @difinition=@difinition+ 
		''alter table ''+'''+@table_full_name+'''+'' add constraint ''+t.name+'' primary key''
		+case when t.type=1 then '' clustered '' when t.type=2 then '' nonclustered '' end
		+'' ''+c.columns+'';
''
	from 
		(select distinct name, type, is_primary_key, is_unique
		from #dif) t
	join #columns c on c.name=t.name
	left join #columns_included ci on ci.name=t.name
	where t.is_primary_key=1
		and 
			(0='+cast(@cluster_only as varchar)+'
			or t.type='+cast(@cluster_only as varchar)+')


	select @difinition=@difinition+ 
		''create ''
		+case when t.is_unique=1 then '' unique '' else '''' end
		+case when t.type=1 then '' clustered '' when t.type=2 then '' nonclustered '' end
		+'' index ''+t.name+'' on ''+'''+@table_full_name+'''
		+'' ''+c.columns+isnull('' include '' +ci.columns, '''')+'';
''
	from 
		(select distinct name, type, is_primary_key, is_unique
		from #dif) t
	join #columns c on c.name=t.name
	left join #columns_included ci on ci.name=t.name
	where t.is_primary_key=0
		and 0='+cast(@key_only as varchar)+'
		and 
			(0='+cast(@cluster_only as varchar)+'
			or t.type='+cast(@cluster_only as varchar)+')

	drop table #dif
	drop table #columns
	drop table #columns_included

	select @difinition
	'
	exec dbo.prc_variable_from_exec @com, @difinition out

end