I have a database where I need to change all period to post information back
1 year.

There are approx 400 tables and most of them have a perpost field where the
Period to post information is stored. I need to take this information back
12 months for all perpost fields.  e.g. Perpost in APDOC table in a line
might be 199912 and I will need to change it to 199812.

Is there a dynamic script that could be created to do this.

Thank you so much for your assistance.

Sam Commar

As always, test before using:

SELECT 'UPDATE ' + t.TABLE_NAME
                 + ' SET perpost = DATEADD(year, -1, perpost) '
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
        AND t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE = 'BASE TABLE'
    AND c.Column_Name = 'perpost'

Should generate a series of update statements, which you could then
paste and execute.

HTH,
Stu

Sam Commar wrote:
Show quoteHide quote

Hi,

use this script... test the script before use in production.

Declare @Table_name VARCHAR(128)
DECLARE @SQL VARCHAR(8000)

DECLARE c1_cur CURSOR FOR
            SELECT Table_name FROM INFORMATION_SCHEMA.COLUMNS
            where Column_name='Perpost'
OPEN C1_Cur

Fetch Next From c1_cur into @Table_name
WHILE @@FETCH_STATUS != -1
BEGIN
    SET @SQL = 'UPDATE '+@Table_name +' SET Perpost=199812 Where
Perpost=199912'
    EXEC(@SQL)
    PRINT @Table_name+' Updated'
    Fetch Next From c1_cur into @Table_name
END
close C1_Cur
deallocate C1_Cur

Thanks
hari
SQL Server MVP
Show quoteHide quote

How to concatenate strings from a column into a single row (with NULL value)?
Single to Multiple columns
Best way to document databases ?
SQL Query
where datetime between 2PM and 3PM
Database Performance query
Query Help
Incease Database size
SQL permission problem
SQL contacts to mobile phone