Check valid Date for all Columns in all tables for current DB

1

Hi,
for ETL-Processing i need a function, who check all columns in all tables with spec. Name (see code) and Data_Type nvarchar. If the Value isn't a valid Dateformat, then printout....
The Datatype is allways nvarchar (because its a staging table)
I have a little codesnippet addet... :slight_smile:
Thanks
Regards Nicole :wink:

;with cte
AS
(
SELECT COLUMN_NAME, TABLE_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (COLUMN_NAME LIKE '%date%' or COLUMN_NAME like '%create%')
and DATA_TYPE = 'nvarchar'
)
Select all columns from all cte.tables where column.value != korrekt Dateformat;

2

check out TRY_PARSE

3

Hi, here I am writing a sample code this hopefully it should work for you.

USE Database_name
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;
4

What is the expected format?

You can try

Select all columns from all cte.tables where column.value not like 'DD/MM/YYYY'