April 10, 2005 at 9:03 pm
My brain's a little fuzzy right now, so HELP! 🙂 This works, but it seems to me that I should be able to integrate the last clause of the query's Select, getting the count of how many fields in a table, into the query body rather than pulling it from another query. Any suggestions?
--ALTER view dbo.vzTableColumnNames_v2 as
select TableName = o.name, ColumnName = c.name, c.ColID,
t.name + '(' + rtrim(cast(c.Length as char(3))) +
case when c.xscale > 0 then
-- '[' + rtrim(cast(c.xprec as char(2))) + --xprec = c.length for numeric data types
',' + rtrim(cast(c.xscale as char(2))) + ')' else ')' end as ColType,
case c.Isnullable when 1 then 'null' else 'not null' end as IsNullable,
case when d.text is null then '' else ('DEFAULT ' + d.text) end as iDefault
, FieldCount
from syscolumns c --field names
inner join sysobjects o --table names
on c.id = o.id
inner join systypes t --data type names
on c.xtype = t.xtype
left join syscomments d --defaults
on c.cdefault = d.id
join vwTableFieldCount fc --field count
on o.name = fc.TableName
where o.xtype = 'U' --user tables
order by o.name, c.colid
/*
create view vwTableFieldCount as
select TableName = o.name, count(c.name) as FieldCount
from syscolumns c --field names
inner join sysobjects o --table names
on c.id = o.id
where o.xtype = 'U' --user tables
group by o.name
*/
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 10, 2005 at 11:46 pm
select TableName = o.name, ColumnName = c.name, c.ColID,
t.name + '(' + rtrim(cast(c.Length as char(3))) +
case when c.xscale > 0 then
-- '[' + rtrim(cast(c.xprec as char(2))) + --xprec = c.length for numeric data types
',' + rtrim(cast(c.xscale as char(2))) + ')' else ')' end as ColType,
case c.Isnullable when 1 then 'null' else 'not null' end as IsNullable,
case when d.text is null then '' else ('DEFAULT ' + d.text) end as iDefault
, FieldCount
from syscolumns c --field names
inner join sysobjects o --table names
on c.id = o.id
inner join systypes t --data type names
on c.xtype = t.xtype
left join syscomments d --defaults
on c.cdefault = d.id
/*
join vwTableFieldCount fc --field count
on o.name = fc.TableName
*/
inner join (select [id], count(*)FieldCount from syscolumns group by [id]) fc on o.id = fc.id
where o.xtype = 'U' --user tables
order by o.name, c.colid
April 11, 2005 at 9:59 am
Thanks, Wz! I've never done that sort of syntax in a join clause but I think I have it puzzled out. I knew it was an easy one, my brain just wasn't cooperating in trying to puzzle it out.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply