列出具有特定日期值的数据库中的所有表 [英] List all tables from database having a specific date value

问题描述

我有一个要求，我必须列出我的数据库中日期类型列中存在特定年份的所有表。例如，我想列出所有年份 2016 的日期记录，我的查询是：

I have a requirement where I have to list all tables of my database in which a specific year is present in date type columns. for example I want to list all tables in which there are some date records having year 2016, my query was :

select  so.name,sc.name
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME') where year(sc.name)=2016

但它不起作用。请帮我解决这个问题。



我尝试过：

but its not working. Please help me with this issue.

What I have tried:

select  so.name,sc.name
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME') where year(sc.name)=2016

推荐答案

在SQL Server中，您可以按如下方式找到这些表：

In SQL Server you can find the tables as follows:

SELECT * FROM information_schema.tables

如果你想在网上尝试一下，这是一个SQLfiddle： SQL小提琴 [ ^ ]

Here is an SQLfiddle if you want to try it out online: SQL Fiddle[^]

这篇关于列出具有特定日期值的数据库中的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！