How do you determine the base data type of a column!

[Kurt]

Does anyone know how to determine the base datatype of a column? I've tried using sp_columns but if there are user defined datatypes on the column it returns that name instead of the base datatype. I've also tries accessing systypes and syscolumns to determine the base datatypes.

Any tips or trick would really be apreciated!

Kurt

[Mohammed.]

Use spt_datatype_info and syscolumns system tables to get the datatype...

Mohammed...


------------
Kurt at 9/28/01 3:49:50 PM

Does anyone know how to determine the base datatype of a column? I've tried using sp_columns but if there are user defined datatypes on the column it returns that name instead of the base datatype. I've also tries accessing systypes and syscolumns to determine the base datatypes.

Any tips or trick would really be apreciated!

Kurt

[Kurt]

Thanks for the reply Mohammed. But it looks like this has been removed in
SQL 2000. I look this up on MSDN on the following page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_23w9.asp

Concerning spt_datatype_info and SQL 2000 Microsoft states:

Removed; no replacement. Remove all references to master.dbo.spt_datatype_info.

Do you have any other ideas that might get the job done?

Thanks again,
Kurt


------------
Mohammed. at 9/28/01 5:47:55 PM

Use spt_datatype_info and syscolumns system tables to get the datatype...

Mohammed...


------------
Kurt at 9/28/01 3:49:50 PM

Does anyone know how to determine the base datatype of a column? I've tried using sp_columns but if there are user defined datatypes on the column it returns that name instead of the base datatype. I've also tries accessing systypes and syscolumns to determine the base datatypes.

Any tips or trick would really be apreciated!

Kurt

[Mohammed.]

Check for sp_Ms_get_type...
Pass the table name and column id...
you will get column name and columntype...

Mohammed.


------------
Kurt at 9/28/01 6:04:43 PM

Thanks for the reply Mohammed. But it looks like this has been removed in
SQL 2000. I look this up on MSDN on the following page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_23w9.asp

Concerning spt_datatype_info and SQL 2000 Microsoft states:

Removed; no replacement. Remove all references to master.dbo.spt_datatype_info.

Do you have any other ideas that might get the job done?

Thanks again,
Kurt


------------
Mohammed. at 9/28/01 5:47:55 PM

Use spt_datatype_info and syscolumns system tables to get the datatype...

Mohammed...


------------
Kurt at 9/28/01 3:49:50 PM

Does anyone know how to determine the base datatype of a column? I've tried using sp_columns but if there are user defined datatypes on the column it returns that name instead of the base datatype. I've also tries accessing systypes and syscolumns to determine the base datatypes.

Any tips or trick would really be apreciated!

Kurt

[Kurt]

That works Mohammed. Thanks!


------------
Mohammed. at 10/1/01 1:53:18 PM

Check for sp_Ms_get_type...
Pass the table name and column id...
you will get column name and columntype...

Mohammed.


------------
Kurt at 9/28/01 6:04:43 PM

Thanks for the reply Mohammed. But it looks like this has been removed in
SQL 2000. I look this up on MSDN on the following page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_23w9.asp

Concerning spt_datatype_info and SQL 2000 Microsoft states:

Removed; no replacement. Remove all references to master.dbo.spt_datatype_info.

Do you have any other ideas that might get the job done?

Thanks again,
Kurt


------------
Mohammed. at 9/28/01 5:47:55 PM

Use spt_datatype_info and syscolumns system tables to get the datatype...

Mohammed...


------------
Kurt at 9/28/01 3:49:50 PM

Does anyone know how to determine the base datatype of a column? I've tried using sp_columns but if there are user defined datatypes on the column it returns that name instead of the base datatype. I've also tries accessing systypes and syscolumns to determine the base datatypes.

Any tips or trick would really be apreciated!

Kurt