Hi All, I have a TIME table and i'm trying to see how the data was loaded into it. Someone has created and loaded a TIME table where and how can i find in what way the person loaded the table. USE [STAGING] GO /****** Object: Table [Reports].[Time] Script Date: 08/08/2012 10:39:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [Reports].[Time]( [ID] [int] IDENTITY(1,1) NOT NULL, [PK_Date] [datetime] NULL, [Date_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Year] [datetime] NULL, [Year_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Quarter] [datetime] NULL, [Quarter_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Month] [datetime] NULL, [Month_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Week] [datetime] NULL, [Week_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Day_Of_Year] [int] NULL, [Day_Of_Year_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Day_Of_Quarter] [int] NULL, [Day_Of_Quarter_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Day_Of_Month] [int] NULL, [Day_Of_Month_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Day_Of_Week] [int] NULL, [Day_Of_Week_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Week_Of_Year] [int] NULL, [Week_Of_Year_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Month_Of_Year] [int] NULL, [Month_Of_Year_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Month_Of_Quarter] [int] NULL, [Month_Of_Quarter_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Quarter_Of_Year] [int] NULL, [Quarter_Of_Year_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'PK_Date' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Date' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'PK_Date' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Date_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Date_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Date_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Year' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Year' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Year' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Year_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Year_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Year_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Quarter' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Quarter' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Quarter' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Quarter_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Quarter_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Quarter_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Month' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Month_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Week' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Week' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Week' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Week_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Week_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Week_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Year' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Day_Of_Year' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Year' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Year_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Day_Of_Year_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Year_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Quarter' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Day_Of_Quarter' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Quarter' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Quarter_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Day_Of_Quarter_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Quarter_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Month' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Day_Of_Month' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Month' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Month_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Day_Of_Month_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Month_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Week' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Day_Of_Week' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Week' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Week_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Day_Of_Week_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Day_Of_Week_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Week_Of_Year' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Week_Of_Year' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Week_Of_Year' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Week_Of_Year_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Week_Of_Year_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Week_Of_Year_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month_Of_Year' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Month_Of_Year' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month_Of_Year' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month_Of_Year_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Month_Of_Year_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month_Of_Year_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month_Of_Quarter' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Month_Of_Quarter' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month_Of_Quarter' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month_Of_Quarter_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Month_Of_Quarter_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Month_Of_Quarter_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Quarter_Of_Year' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Quarter_Of_Year' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Quarter_Of_Year' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Quarter_Of_Year_Name' GO EXEC sys.sp_addextendedproperty @name=N'DSVColumn', @value=N'Quarter_Of_Year_Name' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time', @level2type=N'COLUMN', @level2name=N'Quarter_Of_Year_Name' GO EXEC sys.sp_addextendedproperty @name=N'AllowGen', @value=N'True' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time' GO EXEC sys.sp_addextendedproperty @name=N'DSVTable', @value=N'Time' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time' GO EXEC sys.sp_addextendedproperty @name=N'Project', @value=N'66308771-d48c-46fc-8a07-1f7b8740119e' ,@level0type=N'SCHEMA', @level0name=N'Reports', @level1type=N'TABLE', @level1name=N'Time' Thanks

This question does not really belong to the SSIS forum specifically. I have doubt you will ever find every piece of information about the table unless your DBA had some tracking logs e.g. DDL statements executed. The DBA or any other business person related to this table should be polled.Arthur My Blog

Thanks Arthur, where should i post this , i mean which forum?

The person could have used the script to load that, you will find the script on different website, here is one sample http://oraclespot.wordpress.com/2010/11/30/load_time_dim_dw/ another one http://www.sqlservercentral.com/scripts/Data+Warehousing/65762/ sometime Developers create SP from this code so it can be used later on in different environments. Check the VSS or TSF where your developers save codes or SPs in Database if developer has created SP for loading this table. Thank you Aamirhttp://sqlage.blogspot.com/

Thanks Arthur, where should i post this , i mean which forum? This is a table that typically is used in reporting, BI, but how it was loaded is not really known especially if you do not show example data. If I were you, I would ask a Business Analyst for your business's rules and then figure how to create a package/script/code to load it.Arthur My Blog

This is a Dimension table for a Datamart or Data warehouse which represents Date dimension. this table is loaded only once where each record represents a new date (starting 1900/1/1 onwards). rest of the columns represent the metadata for the date such as the day of the week, day of the year, week of the year etc. you can find alot of scripts online that populate this table search for "create date dimension". Please mark the post as answered if it answers your question