Searching for column - 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 2008 Forums

Transact-SQL (2008)

Searching for column

Author

Topic

jjgurley
Starting Member

8 Posts

Posted - 2014-10-21 : 11:41:55

I'm not an SQL programmer, but we use an EHR system that uses SQL.
We have a problem that the vendor can't be bothered with, where there are some bogus dates in columns(?).

All I know is that there are column(s) buried in the database that have the string "Date" in their name, and the date is something prior to '1900-01-01'.

When I try a Quick Find, the next button is grayed out. If I can just find the errant fields, I might be able to get the vendor to provide a script to fix them.

So. I need to know how to use wild cards (e.g. *Date* ) and how to display values in matching fileds that are < '1900-01-01'.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-21 : 12:30:43

You can search using a query like this:

SELECT 
	s.name AS schemaname,t.name AS TableName,c.name AS ColumnName, y.name AS DataType
FROM sys.columns c
	INNER JOIN sys.tables t ON t.object_id = c.object_id 
	INNER JOIN sys.types y ON y.system_type_id = c.system_type_id
	INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.name LIKE '%date%' AND t.type = 'U'

To look at the data, you can manually select from each of the tables you find, or if there are many, you can construct the query like this (i.e., run the query, copy and paste the results to a query window and run it)

SELECT 
	'SELECT * FROM ' + QUOTENAME(s.NAME) + '.' +QUOTENAME(t.name) +
	' WHERE c.name < ''19000101'''
FROM sys.columns c
	INNER JOIN sys.tables t ON t.object_id = c.object_id 
	INNER JOIN sys.types y ON y.system_type_id = c.system_type_id
	INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.name LIKE '%date%' AND t.type = 'U'

jjgurley
Starting Member

8 Posts

Posted - 2014-10-21 : 12:39:12

The first example produced no results. You probably think I'm smart enough to make substitutions for your generic names/objects.

jjgurley
Starting Member

8 Posts

Posted - 2014-10-21 : 12:40:40

I lied. It did produce results on the correct database - doh!

jjgurley
Starting Member

8 Posts

Posted - 2014-10-21 : 12:46:26

The second script produces output, but the term "c.name" appears as a literal. It's probably supposed to be substitued with the actual column name maybe?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-21 : 13:17:47

Oh yes, sorry about that

SELECT 
	'SELECT * FROM ' + QUOTENAME(s.NAME) + '.' +QUOTENAME(t.name) +
	' WHERE ' + c.name + ' < ''19000101'''
FROM sys.columns c
	INNER JOIN sys.tables t ON t.object_id = c.object_id 
	INNER JOIN sys.types y ON y.system_type_id = c.system_type_id
	INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.name LIKE '%date%' AND t.type = 'U'

jjgurley
Starting Member

8 Posts

Posted - 2014-10-21 : 13:19:53

Getting closer, but not quite.

Msg 402, Level 16, State 1, Line 751
The data types image and varchar are incompatible in the less than operator.

jjgurley
Starting Member

8 Posts

Posted - 2014-10-21 : 13:27:28

Ourr vendor provided a script that fixed some of our fields, and it used " <'1900-01-01' ", which I tried with similar error.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-21 : 14:22:03

The first query I posted shows the data type of the column. Exclude any rows for which the data type is image.

Normally I would say exclude any column that is not one of the datetime data types: DATE, DATETIME, DATETIME2, SMALLDATETIME. That is the safest thing to do. But some people store dates in varchar columns, so you may want to look at varchar data types as well.

jjgurley
Starting Member

8 Posts

Posted - 2014-10-21 : 14:35:44

I confirmed the format is DATETIME. When I run this script (notice I refined the LIKE to eliminate the word "update"), and copy the results, I get a bunch of panes in the result window that are all blank. I have to delete a few of the results of the initial script because they are datetimeoffsets, which are sinteger and blow up the execution.

SELECT
'SELECT * FROM ' + QUOTENAME(s.NAME) + '.' +QUOTENAME(t.name) +
' WHERE ' + c.name + ' < ''1900-01-01'''
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
INNER JOIN sys.types y ON y.system_type_id = c.system_type_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.name LIKE '%datetime%' AND t.type = 'U'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-22 : 08:43:26

If you get blank panes when you run the query that is generated by the query above, that means there are no rows in those tables where the column has values less than 1900-01-01

jjgurley
Starting Member

8 Posts

Posted - 2014-10-22 : 09:31:39

I sort of guessed that. I'm totally puzzled where my dates are coming from. I found some, but without a diagram of the database, it's hard to guess which ones are being used. Thanks an awful lot for your help!

quote:
Originally posted by James K

If you get blank panes when you run the query that is generated by the query above, that means there are no rows in those tables where the column has values less than 1900-01-01

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources