Recipe/Generate Column Names.sql at main · avst96/Recipe · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
use HeartyHearthDB
go

declare @tablename varchar(50) = 'recipe'

select
	concat('@',c.COLUMN_NAME,' ', c.DATA_TYPE,
	case when c.CHARACTER_MAXIMUM_LENGTH is null then '' else concat(' (',c.CHARACTER_MAXIMUM_LENGTH,')') end,
	case when c.TABLE_NAME + 'id' = c.COLUMN_NAME then ' output' else '' end,
	','	)
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = @tablename


declare @insertlist varchar(5000) = ''

select @insertlist = @insertlist + concat(c.COLUMN_NAME,', ')
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = @tablename
and c.COLUMN_NAME <> c.TABLE_NAME + 'Id'

select @insertlist

select @insertlist = ''

select @insertlist = @insertlist + concat('@',c.COLUMN_NAME,', ')
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = @tablename
and c.COLUMN_NAME <> c.TABLE_NAME + 'Id'

select @insertlist

select @insertlist = ''

select concat(c.COLUMN_NAME,' = @',c.COLUMN_NAME,', ')
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = @tablename
and c.COLUMN_NAME <> c.TABLE_NAME + 'Id'