Check what datatype a column is

Have you tried:
exec sp_help 'tablename'

This seems to contain all the info you need.
Anthony

I am aware of sp_help 'tablename', but it provides back information in a way I don't know how to access from t-sql.  I can visually see that it holds the data I want, but I don't know how I can test information against it's results.  More to the point:  How do I run a query against the columns that sp_help returns?  Such as, how do I query it to see if a specific column name is int?

I am aware of sp_help 'tablename', but it provides back information in a way I don't know how to access from t-sql.  I can visually see that it holds the data I want, but I don't know how I can test information against it's results.  More to the point:  How do I run a query against the columns that sp_help returns?  Such as, how do I query it to see if a specific column name is int?

Thanks Scott, this really fills in some of the gaps that I don't know about systypes.  I am pretty sure I can extend some of the logic to handle other types such as 'varchar(16)'

I do wish there was something already written to break apart a type such as 'varchar(16) not null' or 'varchar(  16)   not null'  or all the other variances into into 'varchar', '16', and 'not null'.  But I should be able to parse that.

Thanks again,
Micah

You're right -- you will need to parse the type apart from the length.  The @coltype above should be only the type, VARCHAR, NOT the type and length, VARCHAR(16).

You could, though, do it the other way around, that is format the type and length from the system tables.  I'll post an example as soon as I get time (it's been a BUSY day at work today!)

Here is the main query re-coded to accept an optional length on a char/varchar input parameter (I didn't repeat the entire code, just the main query).  This code also adds a needed CORRECTION/ENHANCEMENT to the query that limits the selected list to columns only; the original query included stored procedure parameters (oops!).


SELECT sc.name
FROM syscolumns sc
INNER JOIN systypes st ON sc.xtype = st.xtype
INNER JOIN sysobjects so ON sc.id = so.id
WHERE so.type = 'U' AND
OBJECT_NAME(sc.id) = CASE WHEN ISNULL(@tablename, '') <> ''
     THEN @tablename ELSE OBJECT_NAME(sc.id) END AND
sc.name = CASE WHEN ISNULL(@columnname, '') <> '' THEN @columnname ELSE sc.name END AND
CASE WHEN @type LIKE '%CHAR%' AND PATINDEX('%[0-9]%', @type) > 0 THEN
RTRIM(st.name) + '(' + CAST(sc.length AS VARCHAR(10)) + ')'
ELSE st.name END = @type AND sc.status & CASE WHEN @ident > 0 THEN 0x80 ELSE sc.status END =
CASE WHEN @ident > 0 THEN 0x80 ELSE sc.status END

Great, the new changes really helped.  I am still interested in the NULL/NOT NULL part of it, and I think I would like the script to be a little more robust with seperating out the parts so that it is not as susceptible to typos or formatting.  

I would like to provide more points for that, though, as I think what you have written so far has already more than answered my question.  

here are some cases that came back with some unexpected results:
CREATE TABLE tableTest (
    [testID] [int] IDENTITY(1,1) NOT NULL,

    [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     CONSTRAINT [PK_mFileFormat]
        PRIMARY KEY  CLUSTERED ( [mFileFormatID] )
        ON [PRIMARY]
) ON [PRIMARY]
GO

sorry that last comment was not completed before I sent it.

Great, the new changes really helped.  I am still interested in the NULL/NOT NULL part of it, and I think I would like the script to be a little more robust with seperating out the parts so that it is not as susceptible to typos or formatting.  

I would like to provide more points for that, though, as I think what you have written so far has already more than answered my question.  

here are some cases that came back with some unexpected results:
CREATE TABLE tableTest (
    [testID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [varchar] (50) NOT NULL)
)

'testid', 'int' -- returns true (ok)
'testid', 'int identiy(1,1)' -- returns true (ok)
'testid', 'int messed up' -- returns true (?)
'description', 'varchar(50)' -- returns true (ok)
'description', 'varchar (50)' -- returns false (bad)

I am sure there are other test cases, but this gives a good idea.

I have a correction to the test cases:
'description', 'varchar (50)' -- returns true (ok)
'description', 'varchar (51)' -- returns true (bad)

That's very true.  I did not attempt to add code to edit the values passed in.  I stuck to the "core" logic.

I guess I'm not sure now what you're trying to do.  I thought you were passing in a parameter containing a sub-set of a single column definition (type, maybe length, and identity, if applicable).  It will obviously be much more difficult to parse a full-scale column definition or entire table definition.

At any rate, you COULD create a table of keywords used in defining columns [and tables], and do a full parse/edit based on that.  Hopefully, though, there is some easier way to achieve your overall goal.  If not, I suggest a separate SP that does nothing but edit and standardize the input, then a second(+) SP that does the actual processing you want to do.

I have a correction to the test cases:
'description', 'varchar (50)' -- returns true (ok)
'description', 'varchar (51)' -- returns true (bad)

Regarding the other test cases (I just saw the final part of the message, as I didn't scroll down enough earlier), yes, the logic I originally posted assumes that the input is fully valid and in a specific format.  That is, it starts with coltype optionally followed IMMEDIATELY by an open paren, length, and closing paren.  (So even VARCHAR(050) would not match VARCHAR(50) in the table).

As I noted before, if you want to do a full parse, which I will be happy to assist with code for, I would create a separate stored proc, pass the raw definition to it, let that SP edit it and place it in a standardized format, several output variables, probably, then pass that standardized format, possibly with several input variables, to any other SPs that need to process the data.

You're right.  It makes sense to just send in a subset and keep it simple.  I was kind of hoping there was some way to process a textual column definition into it's syscolumns and systypes information.

Here is an idea that could possibly make the script very robust easily.  I could dynamically create a temp table with the passed in textual column definition and then use it's syscolumns and systypes information to compare against the table and column in question.

You're right.  It makes sense to just send in a subset and keep it simple.  I was kind of hoping there was some way to process a textual column definition into it's syscolumns and systypes information.

Here is an idea that could possibly make the script very robust easily.  I could dynamically create a temp table with the passed in textual column definition and then use it's syscolumns and systypes information to compare against the table and column in question.

I had that thought too.  The only thing that concerned me was that if there is an error, you won't be able to trap it, the SP will simply abend (fail).  But that's probably as good an edit as any!, since you don't have to write it yourself.