Meta Data Help Needed[:(]

Hi Guys,

I have a DataBase in which I have several Tables.

What I want is an SP or Query which takes as its parameter the "tablename".

The Output Should be a having three fields only.

Field name, DataType Of the Field, Length of the DataType.

For Example

Suppose the StoredProcedure Name is "SP_GetTables"

if i have a table named "tbl_Users" with fields

UserName varchar(50)

UserPass varchar(20)

UserAge int

UserStatus bit

In my program side if I pass the parameter as "tbl_Users" to the StoredProcedure SP_Users,

I should get the O/P as

Field Name DataType Length

UserName varchar 50

UserPass varchar 20

UserAge int

UserStatus bit

Regards,

Naveen.

Here it is,

Createproc SP_GetTables(@.TableNameSysname)
as
Begin
Select
column_name [field name]
, data_type [datatype]
, character_maximum_length [length]
From
INFORMATION_SCHEMA.COLUMNS
Where
table_name= @.tablename
Orderby
ordinal_position
End

|||

HiManivannna.D.Sekaran,

Thanks a lot!!!

Is there a way by which I can pass the DataBase name too.

My requirement is like this.

I want an SP where i can pass DBName and Table Name as Parameters and that SP finds the DB and the table inside it.

The SP must reside in My DB too.

Regards,

Naveen

|||

Createproc SP_GetTables(@.TableNameSysname, @.databaseSysname)
as
Begin
Exec('Select
column_name [field name]
, data_type [datatype]
, character_maximum_length [length]
From
['+ @.database+'].INFORMATION_SCHEMA.COLUMNS
Where
table_name = '''+ @.tablename+'''
Order by
ordinal_position')
End