
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure dbo.p_sys_md_create_pk_fk
	as
declare @table_name sysname
declare @last_table_name sysname
declare @constraint_name sysname
declare @column_name sysname
declare @strtable varchar(max)
declare @primarykey varchar(max)

declare @fForeignKey sysname
declare @fTableName sysname
declare @fColumnName sysname
declare @fReferenceTableName sysname
declare @fReferenceColumnName sysname
--declare @flast_table_name sysname
--declare @last_fReferenceTableName sysname
declare @last_fForeignKey sysname
declare @foreignkey varchar(max)

declare @primaryKeysContants varchar(max)
declare @foreignKeysContants varchar(max)


declare @primary_id int, @max_primary_id int, @min_primary_id int
declare @foreign_id int, @max_foreign_id int, @min_foreign_id int

set nocount on

declare @keysTable table(id int identity(1,1), keyName sysname, is_primary bit, is_foreign bit)

declare @tblprimary table(id int identity(1,1), Table_Name sysname, Constraint_Name sysname, COLUMN_NAME sysname)
declare @tblforeign table(id int identity(1,1), ForeignKey sysname, TableName sysname, ColumnName sysname, ReferenceTableName sysname, ReferenceColumnName sysname)

insert into @tblprimary (	Table_Name,		Constraint_Name,		COLUMN_NAME)
SELECT						KCU.TABLE_NAME,	KCU.CONSTRAINT_NAME,	KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU	ON	KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
															AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
															AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
															AND KCU.TABLE_NAME = TC.TABLE_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY KCU.CONSTRAINT_NAME, KCU.ORDINAL_POSITION

select @min_primary_id = min(id), @max_primary_id = max(id) from @tblprimary

set @primary_id = @min_primary_id

print replicate(char(9),1) + 'public class TableKeys' + char(13) + char(10)
print replicate(char(9),1) + '{' + char(13) + char(10)
print replicate(char(9),2) + 'public KeyValuePair<string, List<string>> PrimaryKey { get; set; }' + char(13) + char(10)
print replicate(char(9),2) + 'public Dictionary<string, ForeignKey> ForeignKeys { get; set; }' + char(13) + char(10)
print replicate(char(9),1) + '}' + char(13) + char(10)

print replicate(char(9),1) + 'public class ForeignKey' + char(13) + char(10)
print replicate(char(9),1) + '{' + char(13) + char(10)
print replicate(char(9),2) + 'public string TableName { get; set; }' + char(13) + char(10)
print replicate(char(9),2) + 'public string ConstraintName { get; set; }' + char(13) + char(10)
print replicate(char(9),2) + 'public List<KeyValuePair<string, string>> Fields { get; set; }' + char(13) + char(10)
print replicate(char(9),1) + '}' + char(13) + char(10)

print replicate(char(9),1) + 'public partial class DBC_PK_FK' + char(13) + char(10)
print replicate(char(9),1) + '{' + char(13) + char(10)
print replicate(char(9),2) + 'public static Dictionary<string, TableKeys> TablesKeys = new Dictionary<string, TableKeys>()' + char(13) + char(10)
print replicate(char(9),2) + '{' + char(13) + char(10)

while @primary_id <= @max_primary_id
begin

	select @table_name = Table_Name, @constraint_name = Constraint_Name, @column_name = COLUMN_NAME
	from @tblprimary
	where id = @primary_id

	if isnull(@last_table_name,'') <> @table_name
	begin
		set @primarykey = replicate(char(9),3) + '{ "' + @table_name + '", new TableKeys()' + char(13) + char(10)
		set @primarykey = @primarykey + replicate(char(9),4) + '{' + char(13) + char(10)
		set @primarykey = @primarykey + replicate(char(9),5) + 'PrimaryKey = new KeyValuePair<string,List<string>>("' + @constraint_name + '", new List<string>(){"' + @column_name + '"'

		set @primaryKeysContants= replicate(char(9),2) + @primaryKeysContants + 'public const string ' + @constraint_name + ' = ' + '''' + @constraint_name + '''' + char(13) + char(10)

		insert into @keysTable(keyName, is_primary, is_foreign) values(@constraint_name, 1, 0)
	end 
	else
	begin
		set @primarykey = @primarykey + ',"' + @column_name + '"'
	end

	set @last_table_name = @table_name

	--char(9)
	--+ char(13) + char(10)

	select @table_name = Table_Name, @constraint_name = Constraint_Name, @column_name = COLUMN_NAME
	from @tblprimary
	where id = @primary_id + 1

	if @primary_id = @max_primary_id or (isnull(@last_table_name,'') <> isnull(@table_name,''))
	begin
		set @primarykey = @primarykey + '}),' + char(13) + char(10)  
		print @primarykey

		print replicate(char(9),5) + 'ForeignKeys = new Dictionary<string,ForeignKey>() ' + char(13) + char(10)  
		print replicate(char(9),5) + '{' + char(13) + char(10)

		delete from @tblforeign

		/*
		select						f.name,		OBJECT_NAME(f.parent_object_id),COL_NAME(fc.parent_object_id, fc.parent_column_id),	OBJECT_NAME (f.referenced_object_id),	COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
		FROM sys.foreign_keys AS f
				inner join sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
		where OBJECT_NAME(f.parent_object_id) = @last_table_name
		*/

		insert into @tblforeign(	ForeignKey, TableName,						ColumnName,											ReferenceTableName,						ReferenceColumnName)
		select						f.name,		OBJECT_NAME(f.parent_object_id),COL_NAME(fc.parent_object_id, fc.parent_column_id),	OBJECT_NAME (f.referenced_object_id),	COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
		FROM sys.foreign_keys AS f
				inner join sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
		where OBJECT_NAME(f.parent_object_id) = @last_table_name
		order by f.name asc

		select @min_foreign_id = min(id), @max_foreign_id = max(id) from @tblforeign

		set @foreign_id = @min_foreign_id

		while @foreign_id <= @max_foreign_id
		begin
			/*
			declare @fForeignKey sysname
			declare @fTableName sysname
			declare @fColumnName sysname
			declare @fReferenceTableName sysname
			declare @fReferenceColumnName sysname
			declare @flast_table_name sysname
			declare @foreignkey sysname
			*/

			select @fForeignKey = ForeignKey, @fTableName = TableName, @fColumnName = ColumnName, @fReferenceTableName = ReferenceTableName, @fReferenceColumnName = ReferenceColumnName
			from @tblforeign
			where id = @foreign_id

			if isnull(@last_fForeignKey,'') <> @fForeignKey
			begin
				set @foreignkey = '' 
				set @foreignkey = @foreignkey + replicate(char(9),6) + '{ ' + '"' + @fForeignKey + '"' + ', new ForeignKey()' + char(13) + char(10)
				set @foreignkey = @foreignkey + replicate(char(9),7) + '{'+ char(13) + char(10)
				set @foreignkey = @foreignkey + replicate(char(9),8) + 'ConstraintName = ' + '"' + @fForeignKey + '",' + char(13) + char(10)
				set @foreignkey = @foreignkey + replicate(char(9),8) + 'TableName = ' + '"' + @fReferenceTableName + '",' + char(13) + char(10)
				set @foreignkey = @foreignkey + replicate(char(9),8) + 'Fields = new List<KeyValuePair<string,string>>()' + char(13) + char(10)
				set @foreignkey = @foreignkey + replicate(char(9),8) + '{' + char(13) + char(10)

				insert into @keysTable(keyName, is_primary, is_foreign) values(@fForeignKey, 0, 1)
			end 
			else
			begin
				set @foreignkey = @foreignkey + ',' + char(13) + char(10)
			end

			set @foreignkey = @foreignkey + replicate(char(9),9) +'new KeyValuePair<string,string>("' + @fColumnName + '","' + @fReferenceColumnName + '")'

			set @last_fForeignKey = @fForeignKey

			select @fForeignKey = ForeignKey, @fTableName = TableName, @fColumnName = ColumnName, @fReferenceTableName = ReferenceTableName, @fReferenceColumnName = ReferenceColumnName
			from @tblforeign
			where id = @foreign_id + 1

			if @foreign_id = @max_foreign_id or (isnull(@last_fForeignKey,'') <> isnull(@fForeignKey,''))
			begin
				set @foreignkey = @foreignkey + char(13) + char(10) + replicate(char(9),8) + '}'+ char(13) + char(10)
				set @foreignkey = @foreignkey + replicate(char(9),7) + '}' + char(13) + char(10)
				set @foreignkey = @foreignkey + replicate(char(9),6) + '}' + case when @foreign_id = @max_foreign_id then '' else ',' end 
				print @foreignkey
			end

			set @foreign_id = @foreign_id + 1
		end

		print replicate(char(9),5) + '}' + char(13) + char(10)
		print replicate(char(9),4) + '}' + char(13) + char(10)
		print replicate(char(9),3) + '}' +  case when @primary_id = @max_primary_id then '' else ',' end + char(13) + char(10)
	end
	
	set @primary_id = @primary_id + 1

end

print replicate(char(9),2) + '};' + char(13) + char(10)

	declare @key_min_id int, @key_max_id int, @key_id int
	declare @keyName sysname

	select @key_min_id = min(id), @key_max_id = max(id) from @keysTable

	set @key_id = @key_min_id

	while @key_id <= @key_max_id
	begin
		select @keyName = k.keyName
		from @keysTable k
		where k.id = @key_id

		print replicate(char(9),2) + 'public const string ' + upper(@keyName) + ' = ' + '"' + @keyName + '"' + ';' + char(13) + char(10)

		set @key_id = @key_id + 1
	end

	

print replicate(char(9),1) + '}' + char(13) + char(10)


GO
