November 19, 2010 at 7:44 am
I was tasked with changing all text data in every table to uppercase in our db. I know I could write a procedure that went through every table and every field to change the case, but I was wondering if anyone had any suggestions or thoughts as to a less code-intensive procedure that would essentially do the same. Possibly a cursor that would go through each table/field (although many suggest avoiding them)? Or trigger?
Thank you ahead of time for all suggestions or examples.
-SQL Server 08'
November 19, 2010 at 8:28 am
Does not make sense to me...
Can't you just convert the data in uppercase in your application code or in reporting tool where you need to show it?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 19, 2010 at 8:31 am
first, it's a creepy requirement to uppercase everything without reviewing the data.
do a backup before you run the results of this query. someone undoubtedly did not think this requirement through.
review the data table by table to make sure it's appropriate.
second, I'd use the metadata to generate the statements, filtering it to try to update only columns that were varchar,char,nvarchar and nchar.
here's my SQL to do exactly that...tables with no columns of that type generate a NULL SQL command for it:
/*--results
name(No column name)
ACACTDETNULL
ACACTSCRUPDATE [dbo].[ACACTSCR] SET TITLE = UPPER(TITLE),DESCRIP = UPPER(DESCRIP),SCRTYPE = UPPER(SCRTYPE)
ACACTTYPUPDATE [dbo].[ACACTTYP] SET DESCRIP = UPPER(DESCRIP)
*/
--code modified from a WayneS XML example:
SELECT DISTINCT
t.name,
'UPDATE [' + schema_name(t. schema_id) + '].[' + t.name + '] SET ' + sq.Columns
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',' + name + ' = UPPER(' + name + ')'
FROM sys.columns sc
WHERE sc.object_id = s.object_id
AND type_name(sc.system_type_id) in( 'varchar','char','nvarchar','nchar')
FOR XML PATH('')),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
Lowell
November 19, 2010 at 8:32 am
Sorry, I am a junior developer and still learning both SQL code and application code. It has been passed down to me as a task to perform this in sql server. I have seen examples of such using jquery and what not, but it is required of me to use sql.
November 19, 2010 at 8:53 am
Old habits, die hard.
Can I remind you of [INFORMATION_SCHEMA].[COLUMNS]? 😀
November 19, 2010 at 8:55 am
after looking through lowell's reply I thought of that (information schema columns)!
I am working on a cursor that will utilize it and will post for suggestions and comments in a few. Thanks for the help and knowledge so far!
November 19, 2010 at 9:20 am
Here it is.
However, something is not right at SET @updatecommand = N'UPPER(' + @id + ')'
DECLARE @id nvarchar(255)
DECLARE @updatecommand nvarchar(255)
DECLARE columnCursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME not like '%aspnet%'
and DATA_TYPE !='bit' and DATA_TYPE !='datetime' and DATA_TYPE !='decimal'
and DATA_TYPE !='int' and DATA_TYPE !='money' and DATA_TYPE !='real'
and DATA_TYPE !='uniqueidentifier' and DATA_TYPE !='datetime2'
-- or use the below depending on preference
-- and DATA_TYPE ='nvarchar' and DATA_TYPE ='varchar' and DATA_TYPE ='ntext'
-- and DATA_TYPE ='char'
OPEN columnCursor FETCH next
FROM columnCursor INTO @id WHILE @@fetch_status=0
BEGIN
SET @updatecommand = N'UPPER(' + @id + ')'
EXECUTE(@updatecommand)
FETCH next FROM columnCursor INTO @id
END
CLOSE columnCursor
DEALLOCATE columnCursor
November 19, 2010 at 10:03 am
Here is what i came up with.
Thoughts? suggestions? corrections?
DECLARE @id nvarchar(255)
DECLARE @updatecommand nvarchar(255)
DECLARE @id2 nvarchar(255)
DECLARE @updatecommand2 nvarchar(255)
DECLARE tableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME NOT LIKE '%aspnet%'
ORDER BY TABLE_NAME
OPEN tableCursor Fetch next
FROM tableCursor INTO @id WHILE @@FETCH_STATUS=0
BEGIN
SET @updatecommand = N'UPDATE ' + @id
EXECUTE @updatecommand
--nested cursor to point at the columns in the pre-selected tables
DECLARE columnCursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @id and DATA_TYPE !='bit' and DATA_TYPE !='datetime' --variable from outer cursor
and DATA_TYPE !='decimal' and DATA_TYPE !='int' and DATA_TYPE !='money'
and DATA_TYPE !='real' and DATA_TYPE !='uniqueidentifier' and DATA_TYPE !='datetime2'
-- or use the below depending on preference
-- and DATA_TYPE ='nvarchar' and DATA_TYPE ='varchar' and DATA_TYPE ='ntext'
-- and DATA_TYPE ='char'
OPEN columnCursor FETCH next
FROM columnCursor INTO @id2 WHILE @@fetch_status=0
BEGIN
SET @updatecommand2 = N'SET @id2 = UPPER(@id2)'
EXECUTE(@updatecommand2)
FETCH next FROM columnCursor INTO @id2
END
CLOSE columnCursor
DEALLOCATE columnCursor
--back to outer cursor. get next table
FETCH next FROM tableCursor INTO @id
END
CLOSE tableCursor
DEALLOCATE tableCursor
GO
November 19, 2010 at 11:04 am
So I am receiving some errors when running the cursor. Can anyone provide some insight on this? The cursor and errors are below. The error states that it cannot find the stored procedure, but the cursor is not in a stored procedure.
I tried to put it into one, and still receive the same errors. Thank you.
CURSOR:
DECLARE @id nvarchar(255)
DECLARE @updatecommand nvarchar(255)
DECLARE @id2 nvarchar(255)
DECLARE @updatecommand2 nvarchar(255)
--Cursor to select tables
DECLARE tableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME NOT LIKE '%aspnet%'
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME;
OPEN tableCursor Fetch next
FROM tableCursor INTO @id WHILE @@FETCH_STATUS=0
BEGIN
SET @updatecommand = N'UPDATE ' + @id + ' '
--EXECUTE @updatecommand
--nested cursor to point at the columns in the pre-selected tables
DECLARE columnCursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @id and DATA_TYPE !='bit' and DATA_TYPE !='datetime' --variable from outer cursor
and DATA_TYPE !='decimal' and DATA_TYPE !='int' and DATA_TYPE !='money'
and DATA_TYPE !='real' and DATA_TYPE !='uniqueidentifier' and DATA_TYPE !='datetime2';
-- or use the below depending on preference
-- and DATA_TYPE ='nvarchar' and DATA_TYPE ='varchar' and DATA_TYPE ='ntext'
-- and DATA_TYPE ='char'
OPEN columnCursor FETCH next
FROM columnCursor INTO @id2 WHILE @@fetch_status=0
BEGIN
SET @updatecommand2 = @updatecommand + N'SET ' + @id2 + ' = UPPER(' + @id2 + ')'
EXECUTE @updatecommand2
FETCH next FROM columnCursor INTO @id2
END
CLOSE columnCursor
DEALLOCATE columnCursor
--back to outer cursor. get next table
FETCH next FROM tableCursor INTO @id
END
CLOSE tableCursor
DEALLOCATE tableCursor
ERRORS:
Msg 2812, Level 16, State 62, Line 41
Could not find stored procedure 'UPDATE table1 SET column1 = UPPER(column1)'.
Msg 2812, Level 16, State 62, Line 41
Could not find stored procedure 'UPDATE table2 SET column2 = UPPER(column2)'.
Msg 2812, Level 16, State 62, Line 41
Could not find stored procedure 'UPDATE table3 SET column3 = UPPER(column3)'.
Msg 2812, Level 16, State 62, Line 41
Could not find stored procedure 'UPDATE table4 SET column4 = UPPER(column4)'.
etc.......
November 19, 2010 at 11:49 am
SOLVED!!!!!!!!
Even though I am now just replying to my own replies, I have found the solution and it operates correctly!!
I was even able to shorten it (removing the nested cursor).
The errors I was receiving were caused by missing () around the variables being executed, i.e. EXECUTE @updateCommand should be EXECUTE(@updateCommand).
I also added in a PRINT to see see what was affected at the time of execution.
Thanks for everyones suggestions.
--change in WHERE clause the TABLE_CATALOG to equal the wanted database
DECLARE @id nvarchar(255)
declare @id2 nvarchar(255)
DECLARE @updateCommand nvarchar(255)
DECLARE columnCursor CURSOR FOR
SELECT Table_Name, Column_Name
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG ='INSERTDATABASENAME' and Table_Name not like '%aspnet%' and DATA_TYPE !='bit' and DATA_TYPE !='datetime'
and DATA_TYPE !='decimal' and DATA_TYPE !='int' and DATA_TYPE !='money'
and DATA_TYPE !='real' and DATA_TYPE !='uniqueidentifier' and DATA_TYPE !='datetime2'
and DATA_TYPE !='timestamp'
-- or use the below depending on preference
-- and DATA_TYPE ='nvarchar' and DATA_TYPE ='varchar' and DATA_TYPE ='ntext'
-- and DATA_TYPE ='char'
ORDER BY TABLE_NAME;
OPEN columnCursor FETCH next
FROM columnCursor INTO @id, @id2 WHILE @@fetch_status=0
BEGIN
SET @updatecommand = N'UPDATE ' + @id + ' SET ' + @id2 + ' = UPPER(' + @id2 + ')'
PRINT (@updatecommand) + '
see below for row(s) affected:'
EXECUTE(@updatecommand)
FETCH next FROM columnCursor INTO @id, @id2
END
CLOSE columnCursor
DEALLOCATE columnCursor
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply