Iterating through columns - 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 2005 Forums

Transact-SQL (2005)

Iterating through columns

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-07 : 13:26:16

I found the following script, by John D Lambert, online for renaming table columns. In this case it places underscores when there are spaces in column names so:


/* Rename columns that begin with a digit, contains a space, or contains a hyphen.
**-------------------------------------------------------------------------------------
** This renaming is useful in situations such as when an application doesn't put 
** brackets around column names when passing queries to a SQL Server database.
**-------------------------------------------------------------------------------------
** Author: John D. Lambert, www.ElfInk.com
**-------------------------------------------------------------------------------------
** If you find this script helpful, please visit www.ElfInk.com and drill down to the 
** SQL/Database stuff I have for sale. Items include humorous and professional
** t-shirts, mugs, hats, underwear, clocks, teddy bears, magnets, stickers, & much more...
**-------------------------------------------------------------------------------------*/

DECLARE Cur1 CURSOR FAST_FORWARD FOR
 SELECT o.name tbl, c.name col
 FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
 WHERE IsNumeric(Left(c.name,1)) = 1 --begins with a digit, prepend an underscore
  OR CharIndex(' ',c.name) > 0 --contains a space, replace with underscore
  OR CharIndex('-',c.name) > 0  --contains a hyphen, replace with underscore
DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN
 IF IsNumeric(Left(@col,1)) = 1 
  SET @sql = 'EXECUTE sp_rename ' + char(39) + @tbl + '.' + @col + char(39) + ', ' + char(39) + '_' + Replace(Replace(@col,' ','_'),'-','_') + char(39) + ', ' + char(39) + 'COLUMN' + char(39)
 ELSE
  SET @sql = 'EXECUTE sp_rename ' + char(39) + @tbl + '.' + @col + char(39) + ', ' + char(39) + Replace(Replace(@col,' ','_'),'-','_') + char(39) + ', ' + char(39) + 'COLUMN' + char(39)
 PRINT @sql
 EXECUTE sp_executesql @sql
 FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1

I have a script which manipulates character fields containing dates in the format 991201 for December 1st, 1999 or 1390202 for February 2nd, 2039 and changes them to 19991201 or 20390202 prior to my changing the column types to datetime:


DECLARE	@MyDateField VARCHAR(100),
	@SQL VARCHAR(8000)

SET	@MyDateField = 'Start_Date'

SET	@SQL = '
UPDATE	dbo.format_marketing_details
SET	' + QUOTENAME(@MyDateField) + ' =	CASE
				WHEN ' + QUOTENAME(@MyDateField) + ' = ''0'' THEN NULL
				WHEN ' + QUOTENAME(@MyDateField) + ' LIKE ''10%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@MyDateField) + ', 2, LEN(' + QUOTENAME(@MyDateField) + ')) 
				ELSE ''19'' + ' + QUOTENAME(@MyDateField) + '
			END
'

EXEC (@SQL)

I wondered, in my newbie way, if it were possible to combine the two so that the resultant script would iterate through all the table columns applying the data manipulation to any column tables whos name contained either 'Date' or 'date'?

It's too complex for my level of understanding right now, but I wondered if anyone here could copy with it? Probably too tough a request ...

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-07 : 13:44:45

DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)

DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl, c.name col
FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE o.xtype='U'
AND lower(c.name) like '%date%'

OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN

SET @SQL = '
UPDATE dbo.' + @tbl + '
SET ' + QUOTENAME(@col) + ' = CASE
WHEN ' + QUOTENAME(@col) + ' = ''0'' THEN NULL
WHEN ' + QUOTENAME(@col) + ' LIKE ''10%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
ELSE ''19'' + ' + QUOTENAME(@col) + '
END
'

PRINT @sql
-- EXECUTE sp_executesql @sql --UNCOMMENT TO ACTUALLY RUN
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-07 : 13:55:01

Wow! I never ceased to be amazed by the knowledge of this forum's users. Many thanks for your reply. I shall learn a lot from picking my way through your solution, in order to educate myself further.

Thanks again.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-07 : 14:03:59

Is the script so designed to be applied to all the tables in the current database, or could it be made to select a particular table?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-07 : 14:08:21

It is designed to loop through all tables with a column name like '%date%'
If you only want it to work on one table change the where clause in the cursor.
o.name = 'YOUR TABLE HERE'

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-07 : 17:36:23

Many thanks Joe. Much appreciated. It's inspired me to read up on curl so I can 'roll my own' in future.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-09 : 10:03:01

I uncommented the EXECUTE line and changed the WHERE clause for a single, named, table so:

DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)

DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl , c.name col

FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE o.name = 'dbo.mortgage_list'
---- replace o.name = '....' with o.xtype='U' for ALL tables in database -------

AND lower(c.name) like '%date%'

OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN

SET @sql = '
UPDATE dbo.' + @tbl + '
SET ' + QUOTENAME(@col) + ' = CASE
WHEN ' + QUOTENAME(@col) + ' = ''0'' THEN NULL
WHEN ' + QUOTENAME(@col) + ' LIKE ''10%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
ELSE ''19'' + ' + QUOTENAME(@col) + '
END
'

PRINT @sql
EXECUTE sp_executesql @sql --UNCOMMENT TO ACTUALLY RUN
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1

But, although the script runs without error(s) it fails to make any changes to the data in the rows. My current knowledge level can't fathom out why. :(

Any pointers much appreciated.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-09 : 10:38:24

Figured it out! I has 'dbo.' as part of my table name. However, the script prefixes that later so it's not needed in the table name definition.

Ooops.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-09 : 11:15:24

I tried altering the column type to datetime as part of the script so:


DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)

DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl , c.name col

FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE o.name = 'format_mortgage_extension' 
---- replace o.name = '....' with o.xtype='U' for ALL tables in database -------

AND lower(c.name) like '%date%'


OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN

SET @sql = '
UPDATE dbo.' + @tbl + '
SET ' + QUOTENAME(@col) + ' = CASE
WHEN ' + QUOTENAME(@col) + ' = ''0'' THEN NULL
WHEN ' + QUOTENAME(@col) + ' LIKE ''10%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
ELSE ''19'' + ' + QUOTENAME(@col) + '
END

ALTER TABLE dbo.' + @tbl + ' 
ALTER COLUMN ' + QUOTENAME{@col) + ' datetime IS NULL
'

PRINT @sql
EXECUTE sp_executesql @sql --UNCOMMENT TO ACTUALLY RUN
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1

but got an error:

"syntax error or access violation".

Can anyone please see why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-10 : 00:18:49

quote:

Originally posted by OldMySQLUser

I tried altering the column type to datetime as part of the script so:


DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)

DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl , c.name col

FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE o.name = 'format_mortgage_extension' 
---- replace o.name = '....' with o.xtype='U' for ALL tables in database -------

AND lower(c.name) like '%date%'


OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN

SET @sql = '
UPDATE dbo.' + @tbl + '
SET ' + QUOTENAME(@col) + ' = CASE
WHEN ' + QUOTENAME(@col) + ' = ''0'' THEN NULL
WHEN ' + QUOTENAME(@col) + ' LIKE ''10%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
ELSE ''19'' + ' + QUOTENAME(@col) + '
END

ALTER TABLE dbo.' + @tbl + ' 
ALTER COLUMN ' + QUOTENAME{@col) + ' datetime IS  NULL
'

PRINT @sql
EXECUTE sp_executesql @sql --UNCOMMENT TO ACTUALLY RUN
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1

but got an error:

"syntax error or access violation".

Can anyone please see why?

you dont need IS here. You need only NULL to define column to be nullable. IS NULL is equivalent to =NULL (equating to NULL). its used in expressions to check whether a fields value is null.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources