CODEWITHSUNDEEP
sqlsql-server
SpringLearner
SpringLearner

Reputation: 13844

How to know which column is created in existing table?

I have around 500 tables in my database and each table is having a minimum of 100 columns.We total 5 person are working in the same database.So whenever requirement arises then a new column or a table is added.What ever I make changes,I keep a record but my colleagues didnt do it.So I am facing problem now what others have created column in the existing table or a new table is created.

So can anybody please tell me is it possible to know whether a new column is added to an existing table and if added what is the column name?

Upvotes: 1

Views: 344

Answers (2)

MANOJ GOPI
MANOJ GOPI

Reputation: 1279

Using the below query you can find the tables which were altered recently.

Query to know the table last altered

SELECT * FROM sys.tables
order by modify_date desc

Query to know the Column altered

SELECT TOP (select count(distinct(TransactionID))
from ::fn_trace_gettable( LEFT((select path from sys.traces where is_default = 1 ),len((select path from sys.traces where is_default = 1 )) - PATINDEX('%\%', reverse((select path from sys.traces where is_default = 1 )))) + '\log.trc', default )
where EventClass in (46,47,164) and EventSubclass = 0 and
DatabaseID <> 2  and
ObjectName='table1' and StartTime>'2015-01-10 00:00:00') [name],[colorder]
FROM [sys].[syscolumns]
where id=(SELECT object_id FROM sys.tables
where name='table1')
order by colorder desc

Note: this query will not work if there was any column dropped or the multiple columns of the table was altered using the SQL server UI but will keep track of multiple alter in the same query

The dropped column can be identified by the colorder. You will find the order will be missing but the column information you will not be able to see.

If you provide the table name and the date time, it gives the columns which were altered with order. If it doesnt return any value then it means there was no change made on the table.

Upvotes: 1

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

May be this query help you

SELECT 
    t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name,
    modify_date, create_date
FROM 
    sys.tables AS t
INNER JOIN 
    sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY 
    modify_date DESC

EDIT

To Audit this, You have to use DDL trigger

Step 1:- Create New Audit Table

CREATE TABLE DDLAudit 
(
  PostTime datetime, DatabaseName varchar(256), Event nvarchar(100), 
  ObjectName varchar(256), TSQL nvarchar(2000), Login varchar(256)
)

Step 2:- Create DDL Trigger

CREATE TRIGGER AuditChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL, Login) 
VALUES
(
    GetDate(),
    @ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
    @ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
    @ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)

Now, Every Changes will be logged in Your DDLAudit. You can Filter out based on datetime filter on PostTime column.

Upvotes: 3

Related Questions