How Do I Get The Data Type Information ...

[MarkDicken]

If I know the following :-
Server Name
Database Name
Table Name
Field Name

How Can I find out its Data Type, e.g. Int, Char (or whatever) ??

Manually I can find out that :-
SVR99.PizzaDB.tblDepartment.DeptCode is an INT

How can I do this within a Stored Procedure / T-SQL ???

Many Thanks In Advance ...

Regards

Mark Dicken
Dublin, Ireland

 

[rajeevnandanmishra]

Hi mark,
Try to use following query
---------------------
select b.name as DataType
from syscolumns a join systypes b on a.type=b.type and a.usertype=b.usertype
where id=object_id('myTableName')
and a.name='myColumnName'
---------------------
It returns you the data type of the column.
You can construct a dyanamic sql in stored procedure to use it for your purpose.
You can also prefix the table names like
myServer.myDatabase..myTable if you are not running this procedure from the local server.

Hope this will solve your problem.

 

[tlbroadbent]

You can also use the INFORMATION_SCHEMA views in SQL 7 and 2000. For example, the following query will list all the columns in table 'mytblname' with data type information. This can be used on any database and table.

select * from information_schema.columns
Where table_name='mytblname'


Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[mukund]

Hi,
You can use a system stored procedure
sp_columns tablename
which will give you a detailed listing of the Columns in the table and their attributes.

Mukund.