Search entire database for date fields
-
I have a database that i need to create a script to search it for date fields, I am looking for where it holds peoples date of birth, i have no idea which table it is held in, so can anyone help create a script to return both which table the date is in along with where i can find the data in the table. The format for the DOB is dd/mm/yyyy. I then need to do an update to roll the DOB's forward 1 year for example 1980 changed to 1981. FYI i was going to use SQL Query Ananlyser to do this, unless anyone recommends doing it a different way.
Many Thanks
Jeff
-
Jeff,
Use this query to find out which columns, and their respective tables, have datetime datatypes.
select table_name, column_name
from information_schema.columns
where data_type in ('datetime','smalldatetime')
Once you have found the table/column combination that you would like to update, you can use the dateadd function to add a year to the column in question.
update
set = dateadd(year,1,)
Hope that helps,
-
Let's only hope that the birthdates are stored in datetime/smalldatetime field, and not a varchar field.
N 56°04'39.16"
E 12°55'05.25" -
Thanks for the suggestion Peter, i have just run the following as suggested:
Select table_name, column_name
from information_schema.columns
where date_type in ('datetime','smalldatetime')
When i run this in Query Analyzer i get the following message:
Server: Msg 207, level 16, State 3, Line 1
Invalid column name 'date_type'
Server: Msg 207, level 16, State 1, Line 1
Invalid column name 'date_type'
Have i done something wrong?
Regards
Jeff
-
Oops,
small typo there. That should be data_type not date_type.
As Peter said, let's hope that the data is actually stored in a datetime column.
-
Jeff
Somehow you have changed data_type to date_type. If it still doesn't work after you've corrected that, chances are you have a case-sensitive collation, in which case you will need to put INFORMATION_SCHEMA.COLUMNS, TABLE_NAME, COLUMN_NAME and DATA_TYPE into upper case.
John
-
Thanks for your responces, and yes it does help if i type correctly!! sorry abou that!!
So i have found my columns to update - not too many of them. How do I do an on mass update by 1year, ideally 1 year minus 1 day?
When i tried adding the line to the existing query of:
update
set = dateadd(year,1,)
i get error:
Server:Msg 156, Level 15, State 1, Line 7
Incorrect Syntax near the keyword 'set'
Line 7 being the set= Am i just being daft again?
-
Yes Jeff...
declare @columns table (id int identity(0, 1), tablename sysname, columnname sysname)
insert @columns
(
tablename,
columnname
)
select isc.table_name,
isc.column_name
from information_schema.columns isc
inner join information_schema.tables ist on ist.table_name = isc.table_name
where isc.data_type in ('datetime', 'smalldatetime')
and ist.table_type = 'base table'
declare @id int,
@errors int,
@sql varchar(2000),
@tablename sysname,
@columnname sysname
select @id = max(id),
@errors = 0
from @columns
begin tran
while @id >= 0 AND @errors = 0
begin
select @tablename = quotename(tablename),
@columnname = quotename(columnname)
from @columns
where id = @id
select @sql = 'UPDATE ' + @tablename + ' set ' + @columnname + ' = DATEADD(year, 1, ' + @columnname + ')',
@id = @id - 1
exec (@sql)
select @errors = @errors + case when @@error <> 0 then 1 else 0 end
end
if @errors > 0
rollback tran
else
commit tran
N 56°04'39.16"
E 12°55'05.25" -
Just a quick note about Peter's solution.
This will perform a mass update on all tables containing a datetime/smalldatetime column.
And I know this is exactly what you wanted.
But I'd just like you to really think about whether this is what you want because it will literally update every table in your database. If you have a table in there with a datetime column that you'd forgotten about, it will update it.
Just a friendly warning
.I'd backup my database before running the script just in case you update a table you didn't mean to.
-
Yeah, I know this would come up. Jeff, if you already know which columns to update, write them down and update them one at a time. If you want to update all at once, use this code
declare @columns table (id int identity(0, 1), tablename sysname, columnname sysname)
insert @columns
(
tablename,
columnname
)
SELECT 'MyTable', 'MyColumn' union all
SELECT 'ThisTable', 'ThatColumn' union all
SELECT 'AnotherTable', 'AnotherColumn'
declare @id int,
@errors int,
@sql varchar(2000),
@tablename sysname,
@columnname sysname
select @id = max(id),
@errors = 0
from @columns
begin tran
while @id >= 0 AND @errors = 0
begin
select @tablename = quotename(tablename),
@columnname = quotename(columnname)
from @columns
where id = @id
select @sql = 'UPDATE ' + @tablename + ' set ' + @columnname + ' = DATEADD(year, 1, ' + @columnname + ')',
@id = @id - 1
exec (@sql)
select @errors = @errors + case when @@error <> 0 then 1 else 0 end
end
if @errors > 0
rollback tran
else
commit tran
N 56°04'39.16"
E 12°55'05.25" -
Thanks for the advice, i have just tried to run the update script but get an error when running the following script:
declare @columns table (id int identity(0, 1), tablename sysname, columnname sysname)
insert @columns
(
tablename,
columnname
)
SELECT 'MyTable', 'MyColumn' union all
SELECT 'ThisTable', 'ThatColumn' union all
SELECT 'AnotherTable', 'AnotherColumn'
declare @id int,
@errors int,
@sql varchar(2000),
@tablename sysname,
@columnname sysname
select @id = max(id),
@errors = 0
from @columns
begin tran
while @id >= 0 AND @errors = 0
begin
select @tablename = quotename(tablename),
@columnname = quotename(columnname)
from @columns
where id = @id
select @sql = 'UPDATE ' + @tablename + ' set ' + @columnname + ' = DATEADD(year, 1, ' + @columnname + ')',
@id = @id - 1
exec (@sql)
select @errors = @errors + case when @@error 0 then 1 else 0 end
end
if @errors > 0
rollback tran
else
commit tran
The error message i get back is:
(3 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'AnotherTable'
Am i missing a trick here???
Thanks
Jeff
-
Jeff
You are indeed missing a trick. The tables MyTable, ThisTable and AnotherTable have to exist in your database. If they don't, substitute them for some tables (and columns) that do. Or, if you've got a lot of tables or columns and you want to use them all, try this (not tested):
INSERT INTO @columns
SELECT t.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
John
-
Thanks for the responce John, i must apologise but i have never even looked at SQL before the other week. I am still confused as to how to update all datetime or smalldatetime records in an entire database. I can sort of see how this works for a single table/column. On Johns post how do i integrate this in to the previous script?
I have ran a script which returns 1418 records containing dates in my database, and all i would like is to update all of these on mass.
Regards
Jeff
-
Jeff
Take the following lines out of the previous script:
insert @columns
(
tablename,
columnname
)
SELECT 'MyTable', 'MyColumn' union all
SELECT 'ThisTable', 'ThatColumn' union all
SELECT 'AnotherTable', 'AnotherColumn'
and substitute my script in their place. This part will populate the @columns table variable with a list of all columns and their respective tables. Don't forget to back up the database before you run the whole script!
John
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply