Monday, April 22, 2013

Creating Dimension Table ==> Time (Example)

Script for Dimension table for Time.

Here Date_ID is the primary key which can be used as the foreign key in the fact tables.



                 [DATE_ID] [bigint] IDENTITY(1, 1)

                                    NOT NULL

                ,[SQL_DATE] [datetime] NOT NULL

                ,[DAY] [smallint] NOT NULL

                ,[DAY_OF_WEEK] [smallint] NULL

                ,[WEEK] [smallint] NOT NULL

                ,[MONTH] [SMALLINT] NOT NULL

                ,[QUARTER] [SMALLINT] NOT NULL

                ,[YEAR] [SMALLINT] NOT NULL

                ,[DAY_OF_YEAR] [SMALLINT] NOT NULL

                ,[DAY_TEXT] [VARCHAR](50) NOT NULL

                ,[MONTH_TEXT] [VARCHAR](50) NOT NULL

                ,[QUARTER_TEXT] [VARCHAR](50) NOT NULL



 declare @date datetime

 declare @enddate datetime

 set @date = CONVERT(datetime,'01-01-2012')

 set @enddate = CONVERT(datetime,'12-31-2025')

 while @date <= @enddate


   insert into DATE_DIM














    @date as sqldate

    ,datepart(D,@date) as [day]

    ,datepart(DW,@date) as [day_of_week]

    ,DATEPART(WK,@date) as [week]

    ,DATEPART (M,@date) as [month]

    ,DATEPART(Q,@date) as [quarter]

    ,DATEPART(DY,@date) as [day_of_year]

    ,DATEPART(Y,@date) as [year]

    ,DATENAME(DW,@date) as day_text

    ,DATENAME(M,@date) as month_text


    set @date = DATEADD(D,1,@date)   



Wednesday, April 3, 2013

Total Number Of Records In each Table

Quite often in our day today activity we need to check the count of records in each table of the database. So instead of firing count(*) against all the table, we can use the DMV's ie inbuild system procedures or functions to get the desired result.

Code :

select  distinct as Name_Schema, as Table_Name, p.row_count as Total_Records from sys.objects o inner join
sys.dm_db_partition_stats p on o.object_id = p.object_id
inner join sys.schemas s on o.schema_id=s.schema_id
where o.type = 'U'

So lets say we want to get the list of tables along with the total records in Adventureworks database.

Below is the output for the same:
Name-Schema Table_Name Total_Records
HumanResources Department 16
HumanResources Employee 290
HumanResources EmployeeAddress 290
HumanResources EmployeeDepartmentHistory 296
HumanResources EmployeePayHistory 316
HumanResources JobCandidate 13
HumanResources Shift 3