LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » General SQL Database Question & Answer » Find newly added columns to sql server using apart from initial creation

Find newly added columns to sql server using apart from initial creation

Options

Previous Topic · Next Topic

Favaz

Posted: Tuesday, December 29, 2009 2:55:18 AM

Rank: Newbie

Joined: 12/29/2009
Posts: 1
Points: 3
Where do you live?: Srilanka

Hi,

i did search the Sys table to track this info but i could not get any Altered info on Columns(but you can get table Modified Date using Trace Log)

I initially created a table using SQL Mngmt Studio then added new cols through a script but when i get create table all the cols are in Create.

Also encountered using same DB in different machine (with same config and version SP of SQL Server) the Create Script behave differently.

if there a way i can find out the newly added Cols to a table after its initial create using system views/table in sql server 2005

Basically how does SQL Server know to get the below script when i get Create table script, i need to know that info i am working on a auto generating script.

DB Generated table script

USE [mcc151209]
GO
/****** Object: Table [dbo].[CONTACT] Script Date: 12/29/2009 14:15:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[CONTACT](
[CONTACTID] [uniqueidentifier] NOT NULL,
...
..
.
[CLASSIFICATION] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[TEAMID] [uniqueidentifier] NULL,
[STAFFCLASSIFICATIONID] [uniqueidentifier] NULL,
[POSITIONDESCRIPTION] [varchar](max) COLLATE Latin1_General_CI_AS NULL DEFAULT (''),
[RESOURCELOCATION] [varchar](300) COLLATE Latin1_General_CI_AS NULL DEFAULT (''),
[RESPONSIBILITIES] [varchar](max) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[CONTACT] ADD [REPORTSTO] [varchar](max) COLLATE Latin1_General_CI_AS NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[CONTACT] ADD [EXTENSION] [varchar](max) COLLATE Latin1_General_CI_AS NULL
ALTER TABLE [dbo].[CONTACT] ADD [ALTERNATENUMBER] [varchar](max) COLLATE Latin1_General_CI_AS NULL
ALTER TABLE [dbo].[CONTACT] ADD [MOBILENUMBER] [varchar](max) COLLATE Latin1_General_CI_AS NULL
ALTER TABLE [dbo].[CONTACT] ADD [NOTES] [varchar](max) COLLATE Latin1_General_CI_AS NULL
ALTER TABLE [dbo].[CONTACT] ADD [IMAGEURL] [varchar](max) COLLATE Latin1_General_CI_AS NULL
/****** Object: Index [PK_CONTACT] Script Date: 12/29/2009 14:15:47 ******/

Thanks in advance

Scott Whigham

Posted: Friday, February 26, 2010 9:00:20 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX

Interesting question. No, I don't know of how you find that out. I looked also in INFORMATION_SCHEMA.COLUMNS and there's no date column. The only logical thing I can think of would be to compare the current ORDINAL_POSITION to the last known ORDINAL_POSITION and return any larger.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.