I am reading the tables to get the columns and then setting a variable i.e DECLARE @VEND_ID nvarchar(max)
This works, but i am hard coding in nvarchar(max).
I am trying to determine how i get the value of the table i.e INT or DATETIMESTAMP and set the value instead of all being set to NVARCHAR
Example: DECLARE @ONEOFCOLUMNS datetime or @ONEOFCOLUMNS INT
Any ideas?
DECLARE @VEND_ID nvarchar(max), @FOUND_VEND_ID nvarchar(max)
SELECT @VEND_ID = ''
SELECT @FOUND_VEND_ID = ''
declare
@Variableskeycounter int,
@VariablesKeyMaxID int,
@SQLKeyString1B NVARCHAR(max),
@SQLVariablesString NVARCHAR(max),
@column_name as varchar(max)
SELECT @VariablesKeyCounter = 1
SELECT @VariablesKeyMaxID = count(*) FROM #TEMP_GET_KEYS
---Get All the Columns the Foreign Keys
DROP TABLE #TEMP_GET_KEYS
select ROW_NUMBER() OVER (
ORDER BY col.column_id)
row_num,
schema_name(tab.schema_id) + '.' + tab.name as [table],
col.column_id,
col.name as column_name,
case when fk.object_id is not null then '>-' else null end as rel,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
pk_col.name as pk_column_name,
fk_cols.constraint_column_id as no,
fk.name as fk_constraint_name,
col.is_nullable AS 'FK_Table_IsNullable_Fl'
INTO #TEMP_GET_KEYS
from sys.tables tab
inner join sys.columns col
on col.object_id = tab.object_id
left outer join sys.foreign_key_columns fk_cols
on fk_cols.parent_object_id = tab.object_id
and fk_cols.parent_column_id = col.column_id
left outer join sys.foreign_keys fk
on fk.object_id = fk_cols.constraint_object_id
left outer join sys.tables pk_tab
on pk_tab.object_id = fk_cols.referenced_object_id
left outer join sys.columns pk_col
on pk_col.column_id = fk_cols.referenced_column_id
and pk_col.object_id = fk_cols.referenced_object_id
where schema_name(tab.schema_id) + '.' + tab.name = 'SCHEMA.YOURTABLE'
-- and schema_name(pk_tab.schema_id) + '.' + pk_tab.name is not null
order by schema_name(tab.schema_id) + '.' + tab.name, col.column_id
SELECT * FROM #TEMP_GET_KEYS
WHILE(@VariablesKeyCounter IS NOT NULL AND @VariablesKeyCounter <= @VariablesKeyMaxID)
BEGIN
SELECT @column_name = column_name FROM #TEMP_GET_KEYS WHERE row_num = @VariablesKeyCounter
SELECT @SQLVariablesString = char(9)+ char(9) + char(9) + char(9) + 'DECLARE @' + @column_name + ' nvarchar(max)' + ',' + ' @FOUND_' + @column_name + ' nvarchar(max)' + char(13) +
char(9)+ char(9) + char(9) + char(9) + 'SELECT @' + @column_name + ' = ' + ''''''
Print @SQLVariablesString
SELECT @SQLKeyString1B = char(9)+ char(9) + char(9) + char(9) + 'SELECT @FOUND_' + @column_name + ' = ' + '''''' + char(13)
Print @SQLKeyString1B
SET @VariablesKeyCounter = @VariablesKeyCounter + 1
END
