-- Copyright (c) 2015 gutlib.com - All rights reserved. Terms are in gutlib.sql
-- You are NOT ALLOWED to modify and/or use this code without author permission
-- Copyright (c) 2015 gutlib.com - All rights reserved. Terms are in gutlib.sql
-- You are NOT ALLOWED to modify and/or use this code without author permission
select ret.*
   into #cx
   from (select ns = 'g', name = 'col') calc1
   cross apply (select calc1.*
      ,name_ = ns + '.' + name
   ) calc2
   cross apply (select calc2.*
      ,id = object_id(name_)
      ,all_name_ = name_ + '_'
   ) calc3
   cross apply (select calc3.*
   ) ret

exec g.vw 'g.col'
go
alter view g.col as 
with obj as (select * from g.obj
   /*{ where 1 = 0 union all select * from g.obj_ }*/
)
,col as (
   select * from sys.columns
   /*{ where 1 = 0 union all select * from tempdb.sys.columns }*/
)
,type as (
   select * from sys.types
   /*{ where 1 = 0 union all select * from tempdb.sys.types }*/
)
select ret.* from g.const
   cross join col
   join type on type.user_type_id = col.user_type_id
   left join obj on obj.id = col.object_id

   cross apply (select
       seq = col.column_id
      ,nil = col.is_nullable
      ,prec = col.precision
      ,len = col.max_length

      ,str = case when type.name in ('char','nchar','varchar','nvarchar','text','ntext') then true else false end
      ,int = case when type.name in ('bit','tinyint','smallint','int','bigint') then true else false end
      ,frac = case when type.name in ('decimal','numeric','real','float','smallmoney','money') then true else false end
      ,dt = case when type.name in ('datetime','date','time','datetime2','datetimeoffset') then true else false end
      ,bin = case when type.name in ('binary','varbinary','image') then true else false end
      ,rune = case when type.name in ('nchar','nvarchar','ntext') then true else false end
      ,uq = false -- TODO: detect if unique constraint or index exists where this is the only column
   ) calc1

   cross apply (select
       tbl = obj.name -- table name that is visible relative to current user schema
      ,calc1.*
      ,tbl_ = obj.name_
   ) calc2

   cross apply (select
       def_len =  case
                     when col.max_length = -1 then 'max'
                     else convert(varchar,col.max_length)
                  end
   ) calc3

   cross apply (select
       col.name
      ,def_type = type.name
         + case when calc2.str = true then 
            '(' + calc3.def_len + ')'
           else
            ''
           end
      ,calc2.*
      ,name_ = tbl_ + '.' + col.name
   ) ret

go -- unit tests
if not exists(select 1 from g.col where name_ = 'g.col.name')
   RAISERROR ('g.col.err.1: Can''t find myself',16,1) WITH NOWAIT

if g.debugging()=1
   select * from g.col where tbl like 'g.%' order by tbl,seq

go -- deriving g.col_ from g.col
exec g.vw 'g.col_'

declare @cmd nvarchar(max)

select @cmd = definition
   from sys.sql_modules,#cx
   where object_id = id

select @cmd = replace(replace(replace(replace(@cmd
   ,'create view','alter view')
   ,'/*{',''),'}*/','')
   ,name_,all_name_)
   from #cx

exec(@cmd)
go
drop table #cx
go
create table #test (
   id int
   ,s varchar(2015)
   ,smax varchar(max)
   ,zip char(5)
   ,uid uniqueidentifier
   ,created datetime
)
go
if not exists(select 1 from g.col_ where tbl = '#test' and name = 'id' and def_type = 'int')
   RAISERROR ('g.col.err.2: Can''t find #test.id int',16,1) WITH NOWAIT
go
if not exists(select 1 from g.col_ where tbl = '#test' and name = 's' and def_type = 'varchar(2015)')
   RAISERROR ('g.obj.err.3: Missing: #test.s varchar(2015)',16,1) WITH NOWAIT
go
if not exists(select 1 from g.col where name_ = 'g.const.true' and def_type = 'bit')
   RAISERROR ('g.obj.err.4: Missing: g.const.true bit',16,1) WITH NOWAIT

go
if g.debugging()=1 begin
   select * from g.col
   select * from g.col_ where tbl is not null order by tbl,seq
end
go
drop table #test
