Check the field existence of a database table

Check the field existence of a database table, if exist get the type, size, decimal ..etc attributes

I need SP

SP

(

@.Tablename varchar(30),

@.Fieldname varchar(30),

@.existance char(1) OUTPUT,

@.field_type varchar(30) OUTPUT,

@.field_size int OUTPUT,

@.field_decimal int OUTPUT

)

as

/* Below check the existance of a @.Fieldname in given @.Tablename */

/* And set the OUTPUT variables */

Thanks

To check existance of a data column, try code below:

IFEXISTS (SELECT *FROMSysObjects soINNERJOINSysColumns scON so.ID = sc.IDWHEREObjectProperty(so.ID,'IsUserTable') = 1AND so.Name ='yourtablename'AND sc.Name ='columnname' )

To get datatype of the data column, try:

SELECT data_typeFROM information_schema.columnsWHERE table_schema ='dbo'AND table_name ='yourtablename'AND column_name ='columnname'

|||

You can also do an sp_Help 'Table' to get all the information.

|||

Hi jackyang,

Thanks for your help.Your first query is running properly but second one has problem

SELECT data_typeFROM information_schema.columnsWHERE table_schema ='dbo'AND table_name ='yourtablename'AND column_name ='columnname'

 does not work

here is 'dbo' static or my databas name (my database name is 'neuron')?

Thanks

|||

You can disregard the table_schema then. It's likely the security schema is not default 'dbo' in your setup.

Just use the code below:

SELECT data_typeFROM information_schema.columnsWHERE table_name ='yourtablename'AND column_name ='columnname'