July 27, 2011 at 10:02 am
I have a query as below, and i want to add a check for datatype as well i.e. if column exists but datatype doesnot match change the dataype
if column and datatype both exists print they match and if column doesnot exist add the column
how can i do so with the query below, i tried checking with @datatype but get an error
DECLARE @tableName VARCHAR(255),
@columnName VARCHAR(255),
@datatype VARCHAR(255)
SELECT
@tableName = OBJECT_NAME(OBJECT_ID),
@columnName = name ,
@datatype=TYPE_NAME(system_type_id)
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'abc'
AND name = 'user'
IF @tablename is not null
PRINT 'The column ' + @columnName + ' with datatype ' + @datatype + ' already exists in table ' + @tableName
ELSE
BEGIN
PRINT 'Column does not exist!'
EXEC ('ALTER TABLE abc ADD user nvarchar(900) NULL;')
PRINT 'The column ' + @columnName + ' with datatype ' + @datatype + 'has been added to ' + @tableName
END
July 27, 2011 at 12:39 pm
Curious what you are trying to do here. Is this just playing or do you have some actual reason for this? Your code is sort of half dynamic and half hard coded values. "Changing" datatype is not as simple as it sounds. To see how you can do this look at the change script generated from SSMS when changing datatypes of an existing table. You really shouldn't need to change datatypes of a column except in rare cases once it is in use.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2011 at 1:52 pm
i want to do this for a user value so it is necessary, i want to change from ncahr to nvarchar which should be essentially the same right?
July 27, 2011 at 1:57 pm
They are different datatypes. Try doing this in SSMS and looking at the script. It creates a new column (with a temp name), updates with old column, drops the old column, renames the new column.
nchar and nvarchar are similar but certainly not the same thing. The hard part of what I am getting at is the way you have your script it will let somebody keep changing datatypes on a column. Doesn't make any sense but there really is nothing that would prevent it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 28, 2011 at 6:13 am
I agree with Sean that you are playing with fire. However....
IF (SELECT data_type FROM Information_Schema.Columns WHERE Table_Name = 'MyTable'
AND Column_Name = 'MyColumn' AND data_type = 'NChar') IS NOT NULL
ALTER TABLE MyTable
ALTER COLUMN MyColumn NVarChar(10);
I use this code when making schema changes during projects. This code goes through rigorous testing (a proper SDLC) to make sure nothing breaks before it even touches Production. I don't know of any reason why someone would want to put it in a stored procedure and allow people to randomly change things in their databases and I do not recommend that practice.
However, someone is going to tell you about that information_schema view, so I figured it might as well be someone who says "if you value your database(s) and your job, use this code with caution!"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply