July 15, 2018 at 12:37 am
HI all,
The famous SearchAllTables proc failed to find date values I know exist in my (DateTime) columns. A quick Google turned up zilch else..
TIA
July 15, 2018 at 1:42 am
can you put code for that proc here? if it is the one I saw before it won't deal with dates datatypes.
July 15, 2018 at 6:26 pm
SearchAllTables from stack overflow explicitly ignores date/datetime datatypes.
peek at the code:
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
modify your version to use datetime data types only, and make sure you have the time down to the millisecond for what you are looking for, or else convert all datatime to date and look for a date that way.
Lowell
July 15, 2018 at 7:44 pm
JaybeeSQL - Sunday, July 15, 2018 12:37 AMHI all,The famous SearchAllTables proc failed to find date values I know exist in my (DateTime) columns. A quick Google turned up zilch else..
TIA
I have to ask... why do you actually need to do such a thing?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2018 at 6:09 am
Hello TIA,
What I use is a stored proc which can do a number of different searches, but it is a rather large routine because of the number of different options.
(All tables/All columns/Specific tables/Specific columns/multiple searchstrings/objectnames/object contents/progress information etc.).
That routine can also generate a body of portable code to use anywhere without the stored procedure, this body of code can be adapted for a specific situation. Below this body adapted for a range search on all tables, all columns with a datatype '%datetime%'.
The code is a bit more extensive than needed here, but that is to make the code more generic and adjustable.
Is this helpfull ? (Or is this a bit late).
Please inform us.
Greetings,
Ben
----------------------------------------------------------------------------------------------------
-- EXEC SP_SearchAll Help, Klant
----------------------------------------------------------------------------------------------------
-- Ben Brugman / Stef ten Bras
-- 20160805
-- Zoek script te gebruiken vanaf SQL 2005 en hoger.
----------------------------------------------------------------------------------------------------
--
-- The search criterium, use percent characters as a wildcard for example SET @SSSSSS = '%fiets%'
--
-- DECLARE @SSSSSS varchar(300) = '%sarbnetfets%' -- not used.
----------------------------------------------------------------------------------------------
-- RANGE search.
DECLARE @SSSSSS datetime = '2017-01-01' -- searchcriterium including.
DECLARE @SSSSS2 datetime = '2018-01-01' -- searchcriterium excluding.
-- select * from calendar
-- HELP Information
-- Parameter @SSSSSS is the searchcriterium, only one is allowed at the time.
-- Within the database all table fields excluding XML and image fields are searched.
-- The result is in table ##SearchResult
-- WARNING. Searches on large databases take time.
----------------------------------------------------------------------------------------------------
-- Declarations
DECLARE @work1 varchar(4000)
DECLARE @work2 varchar(4000)
DECLARE @tabel_naam varchar(300)
DECLARE @column_naam varchar(300)
DECLARE @data_type varchar(300)
DECLARE @tabel_schema varchar(300)
----------------------------------------------------------------------------------------------------
-- Resulttable
-- Drop
IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##SearchResult') DROP TABLE ##SearchResult
--
-- Create
--
CREATE TABLE ##SearchResult(
TABLE_CATALOG varchar(300),
TABLE_NAME varchar(300),
COLUMN_NAME varchar(300),
DATA_TYPE varchar(300),
Tel bigint,
idnr int identity(1,1))
----------------------------------------------------------------------------------------------------
-- The commandstring.
SET @work1 =
'
;
WITH
a AS (
SELECT ''TCTCTC.TTTTTT.CCCCCC'' as KOLOM, count(*) AS Tel-- , -- s.item, s.ITEMNUMBER
FROM [TCTCTC].[TTTTTT] T -- JOIN tempdb.dbo.sp_searchall_split s ON s.spid = IDIDID
WHERE T.[CCCCCC] >= ''SSSSSS'' and T.[CCCCCC] < ''SSSSS2'' -- Remember sarbnetfets
)
INSERT INTO ##SearchResult
SELECT
-- IDIDID,
''TCTCTC'',
''TTTTTT'',
''CCCCCC'',
''<<<DATA_TYPE>>>'',
Tel
-- ,
-- item,
-- ITEMNUMBER,
-- ''<<<PARENT>>>''
FROM A
'
----------------------------------------------------------------------------------------------------
-- The loop over all columns.
-- Use a where clause select (or deselect) specific columns (on name, type etc.)
DECLARE tabellen_cursor CURSOR FOR
SELECT C.table_schema, C.table_name, C.column_name, C.Data_type from information_schema.columns C join information_schema.TABLES T on C.TABLE_NAME = T.TABLE_NAME
where T.table_type = 'BASE TABLE' and DATA_TYPE like '%datetime%'
and DATA_TYPE not in ('xml') and DATA_TYPE not in ('image') and DATA_TYPE not in ('geography')
OPEN tabellen_cursor
FETCH NEXT FROM tabellen_cursor
INTO @tabel_schema, @tabel_naam, @column_naam, @data_type
WHILE @@FETCH_STATUS = 0
BEGIN
SET @work2 = @work1
SET @work2 = REPLACE(@work2,'TCTCTC',@tabel_schema)
SET @work2 = REPLACE(@work2,'TTTTTT',@tabel_naam)
SET @work2 = REPLACE(@work2,'CCCCCC',@column_naam)
SET @work2 = REPLACE(@work2,'SSSSSS',@SSSSSS)
SET @work2 = REPLACE(@work2,'SSSSS2',@SSSSS2)
SET @work2 = REPLACE(@work2,'TSTSTS',@tabel_schema)
SET @work2 = REPLACE(@work2,'<<<DATA_TYPE>>>',@data_type)
print @work2 -- Shows the command for the next column to be searched.
EXEC (@work2)
FETCH NEXT FROM tabellen_cursor
INTO @tabel_schema, @tabel_naam, @column_naam, @data_type
END
CLOSE tabellen_cursor
DEALLOCATE tabellen_cursor
----------------------------------------------------------------------------------------------------
-- Show the result
SELECT * FROM ##SearchResult WHERE Tel > 0 order by idnr
July 17, 2018 at 6:18 am
ben.brugman - Tuesday, July 17, 2018 6:09 AMHello TIA,What I use is a stored proc which can do a number of different searches, but it is a rather large routine because of the number of different options.
(All tables/All columns/Specific tables/Specific columns/objectnames/object contents etc.).
That routine can also generate a body of portable code to use anywhere without the stored procedure, this body of code can be adapted for a specific situation. Below this body adapted for a range search on all tables, all columns with a datatype '%datetime%'.The code is a bit more extensive than needed here, but that is to make the code more generic and adjustable.
Is this helpfull ? (Or is this a bit late).
Please inform us.
Greetings,
Ben
----------------------------------------------------------------------------------------------------
-- EXEC SP_SearchAll Help, Klant
----------------------------------------------------------------------------------------------------
-- Ben Brugman / Stef ten Bras
-- 20160805
-- Zoek script te gebruiken vanaf SQL 2005 en hoger.
------------------------------------------------------------------------------------------------------
-- The search criterium, use percent characters as a wildcard for example SET @SSSSSS = '%fiets%'
--
-- DECLARE @SSSSSS varchar(300) = '%sarbnetfets%' -- not used.
----------------------------------------------------------------------------------------------
-- RANGE search.
DECLARE @SSSSSS datetime = '2017-01-01' -- searchcriterium including.
DECLARE @SSSSS2 datetime = '2018-01-01' -- searchcriterium excluding.-- select * from calendar
-- HELP Information
-- Parameter @SSSSSS is the searchcriterium, only one is allowed at the time.
-- Within the database all table fields excluding XML and image fields are searched.
-- The result is in table ##SearchResult
-- WARNING. Searches on large databases take time.----------------------------------------------------------------------------------------------------
-- Declarations
DECLARE @work1 varchar(4000)
DECLARE @work2 varchar(4000)
DECLARE @tabel_naam varchar(300)
DECLARE @column_naam varchar(300)
DECLARE @data_type varchar(300)DECLARE @tabel_schema varchar(300)
----------------------------------------------------------------------------------------------------
-- Resulttable
-- Drop
IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##SearchResult') DROP TABLE ##SearchResult
--
-- Create
--
CREATE TABLE ##SearchResult(
TABLE_CATALOG varchar(300),
TABLE_NAME varchar(300),
COLUMN_NAME varchar(300),
DATA_TYPE varchar(300),
Tel bigint,
idnr int identity(1,1))
----------------------------------------------------------------------------------------------------
-- The commandstring.
SET @work1 =
'
;
WITH
a AS (
SELECT ''TCTCTC.TTTTTT.CCCCCC'' as KOLOM, count(*) AS Tel-- , -- s.item, s.ITEMNUMBER
FROM [TCTCTC].[TTTTTT] T -- JOIN tempdb.dbo.sp_searchall_split s ON s.spid = IDIDID
WHERE T.[CCCCCC] >= ''SSSSSS'' and T.[CCCCCC] < ''SSSSS2'' -- Remember sarbnetfets
)
INSERT INTO ##SearchResult
SELECT
-- IDIDID,
''TCTCTC'',
''TTTTTT'',
''CCCCCC'',
''<<<DATA_TYPE>>>'',
Tel
-- ,
-- item,
-- ITEMNUMBER,
-- ''<<<PARENT>>>''
FROM A
'----------------------------------------------------------------------------------------------------
-- The loop over all columns.
-- Use a where clause select (or deselect) specific columns (on name, type etc.)DECLARE tabellen_cursor CURSOR FOR
SELECT C.table_schema, C.table_name, C.column_name, C.Data_type from information_schema.columns C join information_schema.TABLES T on C.TABLE_NAME = T.TABLE_NAME
where T.table_type = 'BASE TABLE' and DATA_TYPE like '%datetime%'
and DATA_TYPE not in ('xml') and DATA_TYPE not in ('image') and DATA_TYPE not in ('geography')
OPEN tabellen_cursorFETCH NEXT FROM tabellen_cursor
INTO @tabel_schema, @tabel_naam, @column_naam, @data_typeWHILE @@FETCH_STATUS = 0
BEGIN
SET @work2 = @work1
SET @work2 = REPLACE(@work2,'TCTCTC',@tabel_schema)
SET @work2 = REPLACE(@work2,'TTTTTT',@tabel_naam)
SET @work2 = REPLACE(@work2,'CCCCCC',@column_naam)
SET @work2 = REPLACE(@work2,'SSSSSS',@SSSSSS)
SET @work2 = REPLACE(@work2,'SSSSS2',@SSSSS2)
SET @work2 = REPLACE(@work2,'TSTSTS',@tabel_schema)
SET @work2 = REPLACE(@work2,'<<<DATA_TYPE>>>',@data_type)
print @work2 -- Shows the command for the next column to be searched.
EXEC (@work2)
FETCH NEXT FROM tabellen_cursor
INTO @tabel_schema, @tabel_naam, @column_naam, @data_type
ENDCLOSE tabellen_cursor
DEALLOCATE tabellen_cursor
----------------------------------------------------------------------------------------------------
-- Show the result
SELECT * FROM ##SearchResult WHERE Tel > 0 order by idnr
Thanks, Ben, but I have to ask again.... why would anyone need to do such a thing with a given date, like the OP wants???? I can see (maybe) searching for a particular string in all tables of a database but a date? Why would anyone need to do such a thing, especially since the potential for returning a huge number of rows exists?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2018 at 6:43 am
Jeff Moden - Tuesday, July 17, 2018 6:18 AMThanks, Ben, but I have to ask again.... why would anyone need to do such a thing with a given date, like the OP wants???? I can see (maybe) searching for a particular string in all tables of a database but a date? Why would anyone need to do such a thing, especially since the potential for returning a huge number of rows exists?
Hello Jeff,
Of the tools I build, the routine SP_searchall is the most used (I think). It can do searches for specific string (multiple) with equals or likes. Integer searches, datetime searches.
As a tool builder I do not know for which situations this tool is used, but there are plenty of uses. Sometimes to find a specific string when a table column is not known. But this does apply to datetime fields as wel. It is used for strings/references/integers/datetime fields etc. etc. One thing it is used for a lot is testing where data is all registered and that does apply to strings and datetime fields as wel.
For example is something is valid for a specific time. It is fairly easy to enter the values into an 'application' and then testing where this ends up in the database.
True is the search is not specific enough it can give a huge number of for the count in the result. (Each column is only reported once).
And the search can take long time (complete weekend) for and extensive search on a extensive database.
As said the SP_SearchAll can
-- do multiple strings in one call.
-- multiple searches (different connections) can be done at the same time.
-- Progress can be followed from another connection. (exec sp_searchall progress). (For long searches fairly accurate).
-- Object searches can be done over multiple databases (to find in which database you put a testtable for example).
-- For each column a number of example rows can be shown. (After the search with a seperate call).
-- A statement can be run for each find. (For example a mutate or remove statement).
-- Results are kept in the temp table (until a next search by the same connection).
-- Results can be cleared.
For example I use it to find all stored procedures which are tagged by a specific tag, so I can do maintanance on these stored procedures or remove my 'toolset' when I have finished my job.
I can find specific content, for example a 'tag' that I have placed. Or find something which is displayed in an application but the database location(s) is/are unknown. Or testing that a specific piece of information is removed from all tables.
So I think plenty of uses.
Ben
July 17, 2018 at 9:27 am
I agree that you have a developed a fine tool. I was just wondering if you knew of anyone else that would use it to find a specific date in the database.
Hopefully, the OP will provide a reason. I hope it's something other than "homework" or an "interview question".
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2018 at 2:08 am
Jeff Moden - Tuesday, July 17, 2018 9:27 AMI was just wondering if you knew of anyone else that would use it to find a specific date in the database.
I asked a bit around.
The databases we work with contain a lot of date's. Both in actual functional fields like appointment times as in administrative fields; for example time_created or time_updated.
So searching for specific date / time has been used for :
Finding the tables fields where specific data is stored, where input is known. (This in general is on test / development systems, where there is not a complete understanding of the datamodel or the coding. This can be for understanding the system of for debugging the system.).
Finding what has happened during a specific timeframe. Searching within the timeframe what has happened. For example on a testing machine see who has been working on it when a specific problem occured.
Analysing a production database (a copy), what is the usage of that database. In general we do not have access to the production databases for this type of questions, but we have access to a copy of a production database.
I myself used this to see how the usage of the database was spread over the hours in a week and over the days in a week.
Not all methods were exact, but I also used the capacity of the revolving door of a building to come to an estimate of amount of customers which could be handled. (This gave a fair estimate to me. And for me this gave a limit what was to be expected. Didn't count on the installation of a second revolving door). 🙂
But mostly searching on date fields was considered as one of the options of the searching. General string searching was also done a lot. So date searching was just one of the options.
hope to have answered your question,
Ben
July 19, 2018 at 6:58 am
Thanks, Ben. I appreciate the time on that.
I guess I'm a bit spoiled... we have a couple centralized audit tables (different functionality groups for each one) for the important stuff for us to find the types of things that you've just described. From there, we can do a "fan out" to other tables if needed.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2023 at 10:08 pm
Very old post, but, I wanted to let Jeff know that I am in this exact situation.
We had a SQL server where it lost connection to the time server and began to think it was 2 weeks ahead. This lasted for 10 minutes. In this timeframe, error logs, email logs, backup times ... everything, has a date two weeks into the future. Now I need to find all of the user tables/rows, that have a date datatype that is in this range and correct them. Across all databases/all tables.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply