November 19, 2003 at 8:19 am
Here's something I frequently stick in small development DBs when I'm on a new project. It gives me an easily queriable table so I don't have to putz with the mouse, but can get a list of field names I don't have to type out - I'm rather lazy about typing stuff, but habitually use long self explanatory naming conventions.
I also use it a lot when I am looking for design rules I put on myself - .NET is case sensitive so it's helpful to make sure the field ShippingMethodID is always the same and not ShippingmethodID, I can query to see which tables do not have identity columns, etc.
drop procedure MakeFieldMetaData
go
create procedure MakeFieldMetaData as
--select * into FieldMetaData001 from FieldMetaData
if exists(select * from sysobjects where id=object_ID('dbo.FieldMetaData'))
drop table dbo.FieldMetaData
select distinct
convert(char(50),b.Name) as FieldName
,convert(char(20),case when c.Name = 'sysname ' then 'nvarchar ' else c.Name end) as DataType
--,b.length as StorageLenth
,b.prec as TotalLength
,b.scale as DecimalPlaces
,b.colid
,b.cdefault as DefaultConstraintObjectID
,b.domain as Rule_ConstraintObjectID
,case when b.Status&0x08 = 0x08 then 1 else 0 end as Nullable
,case when b.Status&0x10 = 0x10 then 1 else 0 end as ANSIPadding
,case when b.Status&0x80 = 0x80 then 1 else 0 end as IdentityColumn
,convert(char(50),a.Name) as TableName
,getdate() as InfoDate
into
dbo.FieldMetaData
from
sysobjects as a
inner join syscolumns as b on a.id = b.id
left outer join SysTypes as c on b.xtype = c.xtype
where
(a.Type = 'U' or a.Type = 'v') and a.Name not in ('Diagrams','Tables','dtproperties','SysConstraints') and a.name not like 'FieldMetaData%'
alter table FieldMetaData add FieldDefinition as rtrim(FieldName) + ' ' + rtrim(DataType) + case when DataType like '%char%' or DataType like '%num%' then '(' + rtrim(TotalLength) + case when DecimalPlaces > ' ' then ','+rtrim(DecimalPlaces) else '' end + ')' else '' end + case when Nullable = 1 then ' null' else ' not null' end
-- ParsedType
-- (all suffix of "ID" should be reviewed (identity?))
-- (only one identity field per name)
--
Edited by - cmore on 11/19/2003 08:23:39 AM
November 19, 2003 at 8:41 am
Why not just use the INFORMATION_SCHEMA.TABLES view?
--Jonathan
--Jonathan
November 19, 2003 at 8:58 am
Because I tried it and didn't like it- I wrote this when I needed to review a db for naming conventions, identity column usage, field type usage and matching, etc. and didn't want to do it by looking at diagrams. Once I wrote this I wrote some scripts to query the table with to look for the more common issues. I remember trying to use the SCHEMA tables and not finding (perhaps my mistake) what I was looking for.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply