?
Solved

Check what datatype a column is

Posted on 2003-02-27
16
Medium Priority
?
412 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:mjschehl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
16 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8034639
Have you tried:
exec sp_help 'tablename'

This seems to contain all the info you need.
Anthony
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 8034855
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
 

Author Comment

by:mjschehl
ID: 8035462

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mjschehl
ID: 8035524

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
 

Author Comment

by:mjschehl
ID: 8035670

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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8035996
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8036224
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
 

Author Comment

by:mjschehl
ID: 8036592
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
 

Author Comment

by:mjschehl
ID: 8036622
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
 

Author Comment

by:mjschehl
ID: 8036638
I have a correction to the test cases:
'description', 'varchar (50)' -- returns true (ok)
'description', 'varchar (51)' -- returns true (bad)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8036656
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
 

Author Comment

by:mjschehl
ID: 8036663
I have a correction to the test cases:
'description', 'varchar (50)' -- returns true (ok)
'description', 'varchar (51)' -- returns true (bad)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8036689
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
 

Author Comment

by:mjschehl
ID: 8036725
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
 

Author Comment

by:mjschehl
ID: 8036732
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8036782
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question