Información de los campos de una tabla (corrección)

17/07/2006 - 13:49 por roberto.west | Informe spam
Acabo de encontrar un error en una Query que suelo utilizar mucho para
recuperar información de las columnas de una tabla.

El error estaba en la columna "Identity", esta es la nueva...

Select
'Name' = SC.Name,
'Data Type' = Type_Name(SC.xUserType),
'Size' = Convert(Int, SC.Length),
'Precision' = Case When CharIndex(Type_Name(SC.xType),
'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney') > 0
Then Convert(Char(5),ColumnProperty(SC.Id, SC.Name, 'precision')) Else
'' End,
'Scale' = Case When CharIndex(Type_Name(SC.xType),
'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney') > 0
Then Convert(Char(5),OdbcScale(SC.xType,SC.xScale)) Else '' End,
'PrimaryKey' = Case When SIK.IndId = 1 Then 'Yes' Else 'No' End,
'Indexable' = Case When SIK.IndId = 1 Then 'Yes' When SIK.IndId > 1
Then 'Yes' Else 'No' End,
'Identity' = Case When IsNull(SC.Autoval, 0) <> 0 Then 'Yes' Else 'No'
End,
'Allow Nulls' = Case When SC.IsNullable = 1 Then 'Yes' Else 'No' End,
'Default value' = Case When DF.Text Is Null Then '' Else DF.Text End,
'Computed' = Case When SC.IsComputed <> 0 Then 'Yes' Else 'No' End,
'Formula' = Case When FM.[Text] Is Null Then '' Else FM.[Text] End,
'Allow Empty' = Case ColumnProperty(Object_Id(@table), SC.Name,
'UsesAnsiTrim') When 1 Then 'No' When 0 Then 'Si' Else '(n/a)' End
From
SysColumns SC
Left Join SysComments DF On (SC.cDefault = DF.Id)
Left Join SysComments FM On (SC.Id = FM.Id And SC.ColId = FM.Number)
Left Join SysIndexKeys SIK On (SC.Id = SIK.Id And SC.ColId SIK.ColId)
Where
SC.Id = Object_Id(@table)


enlaces antiguos
http://groups.google.com/group/micr...t&rnum)#bdac24862a40429d
http://groups.google.com/group/micr...27ba16d7fd
 

Leer las respuestas

#1 Alejandro Mesa
17/07/2006 - 14:29 | Informe spam
Trata:

'Identity' = Case When IsNull(SC.Autoval, 0) <> 0 Then 'Yes' Else 'No' End,



...
Case When columnproperty(sc.[id], sc.[name], 'IsIdentity') = 1 Then 'Yes'
Else 'No' End as 'Identity',
...


AMB

"" wrote:

Acabo de encontrar un error en una Query que suelo utilizar mucho para
recuperar información de las columnas de una tabla.

El error estaba en la columna "Identity", esta es la nueva...

Select
'Name' = SC.Name,
'Data Type' = Type_Name(SC.xUserType),
'Size' = Convert(Int, SC.Length),
'Precision' = Case When CharIndex(Type_Name(SC.xType),
'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney') > 0
Then Convert(Char(5),ColumnProperty(SC.Id, SC.Name, 'precision')) Else
'' End,
'Scale' = Case When CharIndex(Type_Name(SC.xType),
'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney') > 0
Then Convert(Char(5),OdbcScale(SC.xType,SC.xScale)) Else '' End,
'PrimaryKey' = Case When SIK.IndId = 1 Then 'Yes' Else 'No' End,
'Indexable' = Case When SIK.IndId = 1 Then 'Yes' When SIK.IndId > 1
Then 'Yes' Else 'No' End,
'Identity' = Case When IsNull(SC.Autoval, 0) <> 0 Then 'Yes' Else 'No'
End,
'Allow Nulls' = Case When SC.IsNullable = 1 Then 'Yes' Else 'No' End,
'Default value' = Case When DF.Text Is Null Then '' Else DF.Text End,
'Computed' = Case When SC.IsComputed <> 0 Then 'Yes' Else 'No' End,
'Formula' = Case When FM.[Text] Is Null Then '' Else FM.[Text] End,
'Allow Empty' = Case ColumnProperty(Object_Id(@table), SC.Name,
'UsesAnsiTrim') When 1 Then 'No' When 0 Then 'Si' Else '(n/a)' End
From
SysColumns SC
Left Join SysComments DF On (SC.cDefault = DF.Id)
Left Join SysComments FM On (SC.Id = FM.Id And SC.ColId = FM.Number)
Left Join SysIndexKeys SIK On (SC.Id = SIK.Id And SC.ColId > SIK.ColId)
Where
SC.Id = Object_Id(@table)


enlaces antiguos
http://groups.google.com/group/micr...t&rnum)#bdac24862a40429d
http://groups.google.com/group/micr...27ba16d7fd


Preguntas similares