Change datatype if not int - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Change datatype if not int

Author

Topic

jaystar
Starting Member

12 Posts

Posted - 2014-03-26 : 10:37:48

Hi I can change a datatype using a query. from a nchar to int. How do I skip the type if it is already a int?#

ALTER TABLE dbo.DocketTB
ALTER COLUMN Docket_Status int

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-26 : 10:41:25

query the INFORMATION_SCHEMA.COLUMNS and check for DATA_TYPE column

KH
[spoiler]Time is always against us[/spoiler]

jaystar
Starting Member

12 Posts

Posted - 2014-03-26 : 11:04:44

Hi could you please provide me with a sample?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-26 : 11:10:35

[code]if not exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'DocketTB' and COLUMN_NAME = 'Docket_Status' and DATA_TYPE = 'int')
begin
ALTER TABLE dbo.DocketTB
ALTER COLUMN Docket_Status int
end[/code]

KH
[spoiler]Time is always against us[/spoiler]

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-26 : 18:53:24

I would definitely use EXISTS here rather than NOT EXISTS. Otherwise, if, for example, the table doesn't exist, the command will try to run and get an error.



IF EXISTS(SELECT 1 FROM sys.columns c WHERE c.name = 'Docket_Status'
AND c.object_id = OBJECT_ID('dbo.DocketTB')
AND c.system_type_id <> (SELECT t.system_type_id FROM sys.types t WHERE t.name = 'int'))
BEGIN
    EXEC('ALTER TABLE dbo.DocketTB ALTER COLUMN Docket_Status int')
END --IF

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-26 : 21:38:59

Good point


if not exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'DocketTB' and COLUMN_NAME = 'Docket_Status' and DATA_TYPE  <> 'int')

alternatively you can check for existing column's data type instead of "<> 'int'"

KH
[spoiler]Time is always against us[/spoiler]

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-27 : 11:11:51

If you don't make the code dynamic, and the table does not exist at all, you will get a compile error on the IF statement and it won't run. You can verify this yourself: just execute the code in your local tempdb.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources