SQL: List of tables having primary key as DateTime

Question

How can I get list of tables having primary key of DataType 'DateTime'?

I couldn't find any proper way to do it.

Did you find any improper ways? Did you find a way to get a list of all tables with primary keys? — Nick.Mc, Oct 06 '17 at 03:47
Like I found this one for Composite keys https://stackoverflow.com/questions/23259915/how-to-get-a-list-of-tables-with-composite-primary-key-in-sql-server — Vikas Vaidya, Oct 06 '17 at 03:50

score 1 · Accepted Answer · answered Oct 06 '17 at 03:54

Based on the this query, I've updated query to meet your requirement :

select 
    t.name as TableName,
    tc.name as ColumnName
   from 
    sys.schemas s 
    inner join sys.tables t   on s.schema_id=t.schema_id
    inner join sys.indexes i  on t.object_id=i.object_id
    inner join sys.index_columns ic on i.object_id=ic.object_id 
                                   and i.index_id=ic.index_id
    inner join sys.columns tc on ic.object_id=tc.object_id 
                             and ic.column_id=tc.column_id
    inner join sys.types ty on tc.system_type_id = ty.system_type_id
where i.is_primary_key=1  and ty.name = 'datetime'

SQL: List of tables having primary key as DateTime

1 Answers1