How to forward demo data dates using a stored procedure?

Question

Hi guys,

I am looking for a clean way to forward some demo data using a stored procedure. The data that I want to forward are date types. Due to the nature of my app, some of the data in my app will only appear when certain dates in the data are in the future. I hope this makes sense. : S

Since my database is ever expanding, I was thinking to write a stored procedure which essentially forwards all dates in all tables in my database that belongs to a demo user account. I will also keep track of the date the demo data was forwarded last. Obviously the stored proc will get run on login of a demo data, and when the difference between last date the demo data was forwarded and the current date has met a certain time difference (e.g. 30 days). This way I do not have to keep altering the script as much.

Now to the technical part:

I am using this to retrieve all the tables in the db:

Select 
    table_name 
from 
    Information_Schema.Tables 
Where 
    TABLE_TYPE like 'BASE TABLE' 
    and table_name not like 'Report_%' 
    and table_name not in ('Accounts', 'Manifest', 'System', 'Users')

What I need is a way to iterate through the table names, find the column names and column types. Then I wish to update all columns in each table that is of type datetime. I have read looping in SQL is not ideal, but I would like to minimise the number of database calls rather than putting this on the serverside code.

Am I going down the wrong path to solve this issue?

Thanks in advance.

Answer 1

I agree with the comment that it might not be a good idea to do this automatically and in a hidden manner, but if you want to you can use this.

(Note this assumes SQL Server)

select T.Name, C.Name 
from sys.tables T
join sys.columns C
on T.object_id = C.object_id
and C.system_type_id = 61 -- I would do a little researcht o make sure 61 is all you need to return here

This will get you a list of all datetime columns, along with the table it is in by name.

Then the way I would accomplish it is to have a cursor which builds the update strings on the fly, and exec them kinda like:

DECLARE @UpdateString varchar(500)
DECLARE @DaysToAdd int
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)

set @DaysToAdd = 10

DECLARE db_cursor CURSOR FOR 
select T.Name, C.Name 
from sys.tables T
join sys.columns C
on T.object_id = C.object_id
and C.system_type_id = 61

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @TableName, @ColumnName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
set @UpdateString = 'Update ' + @TableName + ' set ' + @ColumnName + ' = dateadd(dd, ' + cast(@DaysToAdd as varchar) + ', ' + @ColumnName + ') where ...'
exec(@UpdateString)
    FETCH NEXT FROM db_cursor INTO @TableName, @ColumnName  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

There are many things I don't like about this, the cursor, the fact its behind the scenes, and the exec call, along with I'm unsure how you will "update only the test data" since it will be very hard to write the where clause for a generic table in your database. But I think that will get you started.

On the side maybe you should think about having some test data population script which you can run to insert new data which satisfies your date requirements.