IF object_id(N'ddv_DALTableColumnMetaData', 'V') IS NOT NULL
DROP VIEW ddv_DALTableColumnMetaData
GO
CREATE view ddv_DALTableColumnMetaData
-- select * from ddv_DALTableColumnMetaData where table_name = 'ddeventlog'
AS
select c.TABLE_NAME,c.column_name,
case when rw.FieldName is null then c.COLUMN_NAME else '[' + c.COLUMN_NAME + ']' end COLUMN_NAME2,
char(64) + c.COLUMN_NAME as Column_Name3,
dbo.ddf_columnType(data_type,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE) as fieldType,
dbo.ddf_ColumnNameVB(data_type, c.column_name) as columnNameVB,
dbo.ddf_columnTypeVB (data_type) as ColumnTypeVB,
isnull(columnproperty(object_id(c.table_name), c.column_name,'IsIdentity'),0) as IsIdentity,
c.ORDINAL_POSITION,
COLUMNPROPERTY(OBJECT_ID(c.table_name) ,c.COLUMN_NAME,'IsComputed') as IsComputed,
data_type,CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION,NUMERIC_SCALE,
case
when c.COLUMN_DEFAULT like '%gps_int%' and DATA_TYPE = 'binary' then '{0}'
when c.COLUMN_DEFAULT like '%gps_int%' then '0'
when c.COLUMN_DEFAULT like '%gps_char%' then '""'
when c.COLUMN_DEFAULT like '%gps_date%' then '"1/1/1900"'
when c.COLUMN_DEFAULT like '%gps_money%' then '0'
when c.COLUMN_DEFAULT like '%getutcdate%' then '"1/1/1900"'
when c.COLUMN_DEFAULT like '%getdate%' then 'Now()'
when c.COLUMN_DEFAULT like '%getdate%' then 'Now()'
else replace(replace(c.column_default,'(',''),')','') end as column_default
from INFORMATION_SCHEMA.COLUMNS c
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS con
on con.table_name = c.TABLE_NAME and con.constraint_type = 'PRIMARY KEY'
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
on Con.TABLE_NAME = K.TABLE_NAME
AND Con.CONSTRAINT_NAME = K.CONSTRAINT_NAME and k.column_name = c.COLUMN_NAME
LEFT JOIN DDreservedwords rw on rw.fieldname = c.COLUMN_NAME
where
/* don't mess with timestamp columns */
data_type <> 'timestamp'
GO
grant select on ddv_DALTableColumnMetaData to public