SQL 查找表列的创建日期/时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1425487/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find the date/time a table's column was created
提问by Koekiebox
How can you query SQL Server for the creation date for a SQL Server 2005 table column?
如何查询 SQL Server 以获取 SQL Server 2005 表列的创建日期？
I tried the sp_columns [tablename]to get that information, but the creation date wasn't included in this stored procedure.
我尝试sp_columns [tablename]获取该信息，但此存储过程中未包含创建日期。
How can this be done?
如何才能做到这一点？
采纳答案by Beatles1692
There's this system table named sys.Columns that you can get columns information from it. if you want to see columns of a particular table you can do as follows:
有一个名为 sys.Columns 的系统表，您可以从中获取列信息。如果要查看特定表的列，可以执行以下操作：
SELECT col.* from sys.objects obj
inner join sys.columns col
on obj.object_Id=col.object_Id
and obj.Name=@tableName
Or you can get table information like this:
或者你可以得到这样的表信息：
SELECT * FROM sys.objects WHERE Name=@tableName
but I couldn't find any information on creation date of a column.
但我找不到有关列创建日期的任何信息。
Updated: This might help.
更新： 这可能会有所帮助。
回答by Solomon Rutzky
SQL Server does not keep track of specific changes to tables. If you want or need this level of detail, you need to create a DDL Trigger (introduced in SQL Server 2005) that can trap certain specific events or even classes of events, and log those changes to a history table that you create.
SQL Server 不跟踪表的特定更改。如果您想要或需要这种级别的详细信息，您需要创建一个 DDL 触发器（在 SQL Server 2005 中引入），它可以捕获某些特定事件甚至事件类别，并将这些更改记录到您创建的历史表中。
DDL Triggers are "after" triggers; there is no "instead of" option. However, if you want to disallow an action, you can just issue a ROLLBACKand that will cancel what happened.
DDL 触发器是“之后”触发器；没有“代替”选项。然而，如果你想禁止一个动作，你可以只发出 aROLLBACK并取消发生的事情。
The MSDN page for DDL Triggershas a lot of good information regarding how to trap either specific events (i.e. ALTER TABLE) and using the EVENTDATAfunction, which returns XML, to get the specifics of what event fired the trigger, including the exact SQL query that was executed. In fact, the MSDN page for Use the EVENTDATA Functioneven has simple examples of creating a DDL trigger to capture ALTER TABLEstatements (in the "
ALTER TABLE and ALTER DATABASE Events" section) and creating a DDL trigger to capture the events to a log table (in the "Example" section). Since all ALTER TABLEcommands will fire this trigger, you need to parse out which ones are specific to what you are looking for. And, maybe now that you know that this is an option, capturing more than just adding columns is desired (i.e. dropping columns, changing the datatype and/or NULLability, etc).
DDL 触发器的 MSDN 页面有很多关于如何捕获特定事件（即ALTER TABLE）和使用EVENTDATA返回 XML的函数来获取触发触发器的事件的详细信息的好信息，包括执行的确切 SQL 查询. 事实上，使用 EVENTDATA 函数的 MSDN 页面甚至有创建 DDL 触发器以捕获ALTER TABLE语句（在“ALTER TABLE 和 ALTER DATABASE 事件”部分）和创建 DDL 触发器以将事件捕获到日志表的简单示例（在“示例”部分）。由于所有ALTER TABLE命令将触发此触发器，您需要解析哪些特定于您要查找的内容。而且，也许现在您知道这是一个选项，需要捕获的不仅仅是添加列（即删除列、更改数据类型和/或可空性等）。
It should be noted that you can create a DLL trigger ON ALL SERVERfor database-scoped events such as ALTER_TABLE.
应该注意的是，您可以ON ALL SERVER为数据库范围的事件创建一个 DLL 触发器，例如ALTER_TABLE.
If you want to see the structure of the XML for any event or event class, go to:
如果要查看任何事件或事件类的 XML 结构，请访问：
http://schemas.microsoft.com/sqlserver/2006/11/eventdata/
http://schemas.microsoft.com/sqlserver/2006/11/eventdata/
and click on the "Current version:" link. If you want to see a specific event or event class, just do a search (usually Control-F in the browser) on the event name that would be used in the trigger's "FOR" clause (including the underscore). The following is the schema for the ALTER_TABLEevent:
并单击“当前版本：”链接。如果您想查看特定的事件或事件类，只需在触发器的“FOR”子句（包括下划线）中使用的事件名称上进行搜索（通常在浏览器中使用 Control-F）。以下是ALTER_TABLE事件的架构：
<xs:complexType name="EVENT_INSTANCE_ALTER_TABLE">
<xs:sequence>
<!-- Basic Envelope -->
<xs:element name="EventType" type="SSWNAMEType"/>
<xs:element name="PostTime" type="xs:string"/>
<xs:element name="SPID" type="xs:int"/>
<!-- Server Scoped DDL -->
<xs:element name="ServerName" type="PathType"/>
<xs:element name="LoginName" type="SSWNAMEType"/>
<!-- DB Scoped DDL -->
<xs:element name="UserName" type="SSWNAMEType"/>
<!-- Main Body -->
<xs:element name="DatabaseName" type="SSWNAMEType"/>
<xs:element name="SchemaName" type="SSWNAMEType"/>
<xs:element name="ObjectName" type="SSWNAMEType"/>
<xs:element name="ObjectType" type="SSWNAMEType"/>
<xs:element name="Parameters" type="EventTag_Parameters" minOccurs="0"/>
<xs:element name="AlterTableActionList" type="AlterTableActionListType" minOccurs="0"/>
<xs:element name="TSQLCommand" type="EventTag_TSQLCommand"/>
</xs:sequence>
</xs:complexType>
Here is a very simple test to see how this works and what the resulting EventData XML looks like:
这是一个非常简单的测试，用于查看其工作原理以及生成的 EventData XML 的外观：
IF (EXISTS(
SELECT *
FROM sys.server_triggers sst
WHERE sst.name = N'CaptureAlterTable'
))
BEGIN
DROP TRIGGER CaptureAlterTable ON ALL SERVER;
END;
GO
CREATE TRIGGER CaptureAlterTable
ON ALL SERVER -- capture events for all databases
FOR ALTER_TABLE -- only capture ALTER TABLE events
AS
PRINT CONVERT(NVARCHAR(MAX), EVENTDATA()); -- Display in "Messages" tab in SSMS
GO
First we create a simple, real table in tempdb (these events are not captured for temp tables):
首先我们在 tempdb 中创建一个简单的、真实的表（这些事件不会被临时表捕获）：
USE [tempdb];
CREATE TABLE dbo.MyAlterTest (Col2 INT NULL);
Next we add a column. We do this from a different database to make sure that the XML captures the database where the object exists instead the current database. Please note the casing of the words alTeR Table tempDB.dbo.MyALTERTest ... DATEcreatedto compare with what is in the XML.
接下来我们添加一列。我们从不同的数据库执行此操作，以确保 XML 捕获对象所在的数据库而不是当前数据库。请注意alTeR Table tempDB.dbo.MyALTERTest ... DATEcreated要与 XML 中的内容进行比较的单词的大小写。
USE [master];
alTeR Table tempDB.dbo.MyALTERTest ADD DATEcreated DATETIME NOT NULL;
You should see the following in the "Messages" tab (comments added by me):
您应该在“消息”选项卡中看到以下内容（我添加的评论）：
<EVENT_INSTANCE>
<EventType>ALTER_TABLE</EventType>
<PostTime>2014-12-15T10:53:04.523</PostTime>
<SPID>55</SPID>
<ServerName>_{server_name}_</ServerName>
<LoginName>_{login_name}_</LoginName>
<UserName>dbo</UserName>
<DatabaseName>tempdb</DatabaseName> <!-- casing is based on database definition -->
<SchemaName>dbo</SchemaName>
<ObjectName>MyAlterTest</ObjectName> <!-- casing is based on object definition -->
<ObjectType>TABLE</ObjectType>
<AlterTableActionList>
<Create>
<Columns>
<Name>DATEcreated</Name> <!-- casing is taken from executed query -->
</Columns>
</Create>
</AlterTableActionList>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/>
<CommandText>alTeR Table tempDB.dbo.MyALTERTest ADD DATEcreated DATETIME NOT NULL;
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
It would have been nice if the per-column details (i.e. NULL / NOT NULL, datatype, etc) were captured instead of just the name, but if need be, those can be parsed out of the CommandTextelement.
如果捕获每列详细信息（即 NULL / NOT NULL、数据类型等）而不仅仅是名称，那就太好了，但如果需要，可以从CommandText元素中解析出这些细节。
回答by Ambala Chandrashekar
SELECT so.name,so.modify_date
FROM sys.objects as so
INNER JOIN INFORMATION_SCHEMA.TABLES as ist
ON ist.TABLE_NAME=so.name where ist.TABLE_TYPE='BASE TABLE' AND
TABLE_CATALOG='dbName' order by so.modify_date desc;
回答by Martin P
Bit late to the party but found something that helped for me. If you can say the column is the last change to the table then sys.tables(modify_date)is workable.
参加聚会有点晚，但找到了对我有帮助的东西。如果您可以说该列是对表的最后一次更改，则sys.tables(modify_date)是可行的。
回答by TheVillageIdiot
I think there is no way of getting modification or creation date of individual columns per se. The queries given in thisand thisanswer returns the dates related to Table containing the column not the column. Because sys.columns table have same id for all the columns in the table. You can verify this by running this query
我认为没有办法获得单个列本身的修改或创建日期。此和此答案中给出的查询返回与包含列而不是列的表相关的日期。因为 sys.columns 表对于表中的所有列都具有相同的 id。您可以通过运行此查询来验证这一点
select col.object_id, col.name, col.column_id
from sys.columns col
where col.object_id =
(select o.object_id from sys.objects o where o.Name = @tableName)
回答by MartW
I don't think this information is available unless you have something that can browse the transaction logs, like Log Explorer. It's not a tsql thing.
我不认为此信息可用，除非您有可以浏览事务日志的东西，例如日志资源管理器。这不是 tsql 的事情。
回答by Ralph Lavelle
SELECT obj.create_date
from sys.objects obj
inner join sys.columns col on obj.object_Id=col.object_Id
WHERE col.name = @columnName
and obj.Name=@tableName
building on the previous answer, but giving only the create date of the column
建立在上一个答案的基础上，但仅提供该列的创建日期
回答by mp01702
I had an issue where I had to replicate tables to another server and I wanted to do this exact thing but there was nothing in the sys tables that showed me when a column was added or changed. Since I know my current table has the correct columns I did the following in a do loop:
我遇到了一个问题，我必须将表复制到另一台服务器，我想做这件事，但是当添加或更改列时，sys 表中没有任何内容向我显示。由于我知道当前表具有正确的列，因此我在 do 循环中执行了以下操作：
declare @CurrentTableCount int = 0,
@SchemaCheckTableCount = 0
select top 1 * into TableNameSchemaCheck from OpenQuery
(Database, 'select * from TableName where 1 = 2')
select @CurrentTableCount = (select count(1) from sys.syscolumns c
inner join sys.sysobjects o on c.id = o.id
where o.xtype = 'U'
and o.name = 'TableName')
select @SchemaCheckTableCount = (select count(1) from sys.syscolumns c
inner join sys.sysobjects o on c.id = o.id
where o.xtype = 'U'
and o.name = 'TableNameSchemaCheck')
if @CurrentTableCount <> @SchemaCheckTableCount
begin
insert into SchemaCheckLog
values ('CompanyContext', getdate(), 'Schema for TableName table has changed.', 0)
end
drop table TableNameSchemaCheck
end
I built a procedure that emails me results in that table and then I know the schema of the source table has changed and I need to change it in the target.
我构建了一个过程，通过电子邮件向我发送该表中的结果，然后我知道源表的架构已更改，我需要在目标中更改它。
You could then expand on this and find the column names that are different but my use case doesn't require that.
然后，您可以对此进行扩展并找到不同的列名称，但我的用例不需要。
