Check what datatype a column is

How can I check to see what datatype a column is?

I want to run a script something like:

declare @result bit
exec columnType 'tablename', 'columnname', 'int null identity(1,1)', @result output

if (@result=0)
begin
    alter table [tablename]
       drop column [columnName]

    alter table [tablename]
       add [columnName] int null identity(1,1)
end

I would try to just check the syscolums table, but I don't know how to turn the string 'int null identity(1,1)' into a set of parameters to search the columns of the syscolumns table with.
mjschehlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Have you tried:
exec sp_help 'tablename'

This seems to contain all the info you need.
Anthony
0
Scott PletcherSenior DBACommented:
Maybe try some code like the following as part of a SP/function:

DECLARE @tablename VARCHAR(64)  --param into SP/function
DECLARE @columnname VARCHAR(64)  --param into SP/function
DECLARE @coltype VARCHAR(100)  --param into SP/function
DECLARE @type SYSNAME
DECLARE @byte TINYINT
DECLARE @ident BIT

--SET @tablename = 'table1'
--SET @columnname = 'id'
SET @coltype = 'int identity'

IF CHARINDEX(' ', @coltype) = 0
     SET @type = @coltype
ELSE
BEGIN
     SET @byte = CHARINDEX(' ', @coltype)
     SET @type = LEFT(@coltype, @byte - 1)
     SET @coltype = SUBSTRING(@coltype, @byte + 1, LEN(@coltype) - @byte)
END --ELSE
IF LEFT(@coltype, 8) = 'IDENTITY'
     SET @ident = 1    

SELECT sc.name
FROM syscolumns sc
INNER JOIN systypes st ON sc.xtype = st.xtype
WHERE 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
st.name = @type AND sc.status & CASE WHEN @ident > 0 THEN 0x80 ELSE sc.status END =  --0x80 = identity column
CASE WHEN @ident > 0 THEN 0x80 ELSE sc.status END

RETURN @@ROWCOUNT
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mjschehlAuthor Commented:

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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mjschehlAuthor Commented:

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?
0
mjschehlAuthor Commented:

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


0
Scott PletcherSenior DBACommented:
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!)
0
Scott PletcherSenior DBACommented:
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
0
mjschehlAuthor Commented:
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
0
mjschehlAuthor Commented:
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.
0
mjschehlAuthor Commented:
I have a correction to the test cases:
'description', 'varchar (50)' -- returns true (ok)
'description', 'varchar (51)' -- returns true (bad)
0
Scott PletcherSenior DBACommented:
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.
0
mjschehlAuthor Commented:
I have a correction to the test cases:
'description', 'varchar (50)' -- returns true (ok)
'description', 'varchar (51)' -- returns true (bad)
0
Scott PletcherSenior DBACommented:
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.
0
mjschehlAuthor Commented:
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.
0
mjschehlAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.