Help with command for updating unknown tables

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)

Help with command for updating unknown tables

Author

Topic

jeffnc
Starting Member

14 Posts

Posted - 2008-10-17 : 10:31:38

I have the following query so far

SELECT columns.table_name, columns.column_name
FROM INFORMATION_SCHEMA.COLUMNS as [columns]
INNER JOIN INFORMATION_SCHEMA.TABLES AS [tables] ON (columns.table_name = tables.table_name)
WHERE columns.data_type='datetime' AND tables.table_type = 'BASE TABLE'

This (I believe) gives me every column in the database with a datetime type. I now want to adjust each of those dates with a new calculated date. I'm not sure of the best way to construct the command. I was thinking

WHILE SELECT ...(above)
BEGIN
UPDATE columns.table_name SET columns.column_name = (new val)
END

But not sure of syntax or other better ways, thx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-17 : 10:39:48

Make use of the result

SELECT 'Update '+columns.table_name+' SET '+columns.column_name+' = (new val)'
FROM INFORMATION_SCHEMA.COLUMNS as [columns]
INNER JOIN INFORMATION_SCHEMA.TABLES AS [tables] ON (columns.table_name = tables.table_name)
WHERE columns.data_type='datetime' AND tables.table_type = 'BASE TABLE'

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 10:39:56

[code]CREATE TABLE #Temp
(
ID int identity(1,1),
table_name varchar(20),
column_name varchar(20)
)

DECLARE @sql varchar(2000),@ID int,@Table varchar(50),@Column varchar(50)
INSERT INTO #Temp (table_name,column_name)
SELECT columns.table_name, columns.column_name
FROM INFORMATION_SCHEMA.COLUMNS as [columns]
INNER JOIN INFORMATION_SCHEMA.TABLES AS [tables] ON (columns.table_name = tables.table_name)
WHERE columns.data_type='datetime' AND tables.table_type = 'BASE TABLE'
SELECT @ID=MIN(ID)
FROM #Temp

WHILE @ID IS NOT NULL
BEGIN
SELECT @Table=table_name,@Column=column_name
FROM #Temp
WHERE ID=@ID

SET @Sql='UPDATE '+ @Table + ' SET '+ @Column+ ' = yourvalue'

EXEC(@sql)
SELECT @ID=MIN(ID)
FROM #Temp
WHERE ID >@ID
END[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-17 : 10:56:59

dynamic sql is one option - generating the update code is another:
(replace getDate() with your expression)


select replace('update ' + table_name + ' set ' + ca.col, 'set ,', 'set')
from   information_schema.tables t
cross apply (
       select ', ' + column_name + ' = getdate()'
       from information_schema.columns 
       where  table_name = t.table_name
       and    data_type = 'datetime'
       for xml path('')
       )ca (col)
where  table_type = 'base table'
and    ca.col is not null

Be One with the Optimizer
TG

jeffnc
Starting Member

14 Posts

Posted - 2008-10-17 : 18:11:45

I went with Visakh's idea, especially since I needed a column value to calculate a new value anyway, and this seemed most readable.

However, wouldn't it better to use a cursor rather than the added ID column to the temp table? I don't know, I'm just asking. The loop indexing logic looks a tad convoluted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources