Last Thursday night I had the pleasure of speaking with Gregory Kramer and his friends at the Madison, WI Power BI User Group. We took an introductory look at how Microsoft’s acquisition of Revolution Analytics is making serious waves across the Microsoft data platform. We also walked through leveraging R in Power BI and SQL Server 2016.
It’s always kind of a pain to have to hunt down all those foreign key references so you can address the issues. So I put this script together (based on a script found on StackOverflow) in order to help me find all the required information related to a particular column in a specified table. I’m mostly posting this for my own reference later and for anyone else that may find this useful, so enjoy!
SELECT OBJECT_NAME(f.object_id) as ForeignKeyConstraintName, OBJECT_NAME(f.parent_object_id) TableName, COL_NAME(fk.parent_object_id,fk.parent_column_id) ColumnName, OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName, COL_NAME(fk.referenced_object_id,fk.referenced_column_id) as ReferencedColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fk ON f.OBJECT_ID = fk.constraint_object_id INNER JOIN sys.tables t ON fk.referenced_object_id = t.object_id WHERE OBJECT_NAME(fk.referenced_object_id) = 'your table name' and COL_NAME(fk.referenced_object_id,fk.referenced_column_id) = 'your key column name'
Here’s a picture of what the results look like. I ran this query against the ReportServer database used for SSRS in case you were wondering.
If you want to find every Foreign Key in your database, just eliminate the Where clause to bring back all the FKs. Hopefully you found this as useful as I did.
Occasionally I find myself needing to generate a small table with a list of dates for various queries I may be running. To do this, I usually leverage the Date dimension since I do most of my work in BI environments with a traditional data warehouse. But if you don’t have access to a Date dimension table, you can quickly generate a date table using Continue reading Generate a Date Table via Common Table Expression (CTE)
I do quite a bit of training for Pragmatic Works so I find myself working a lot with the AdventureWorksDW data warehouse database. AdventureWorksDW is a great test database if you’re wanting to demonstrate a concept or test a theory, but one of the things that has always bugged me is that the date dimension (dbo.DimDate) has holes in the data! Having a complete date dimension is important when working with SSAS. Well yesterday was the final straw, so I put together the following script that will fill in the missing dates in DimDate. You can specify a start date and end date and make the AdventureWorksDW DimDate date dimension as big as you like. Enjoy!
BEGIN TRAN declare @startdate date = '2005-01-01', @enddate date = '2014-12-31' IF @startdate IS NULL BEGIN Select Top 1 @startdate = FulldateAlternateKey From DimDate Order By DateKey ASC END Declare @datelist table (FullDate date) while @startdate <= @enddate Begin Insert into @datelist (FullDate) Select @startdate Set @startdate = dateadd(dd,1,@startdate) end Insert into dbo.DimDate (DateKey, FullDateAlternateKey, DayNumberOfWeek, EnglishDayNameOfWeek, SpanishDayNameOfWeek, FrenchDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear, WeekNumberOfYear, EnglishMonthName, SpanishMonthName, FrenchMonthName, MonthNumberOfYear, CalendarQuarter, CalendarYear, CalendarSemester, FiscalQuarter, FiscalYear, FiscalSemester) select convert(int,convert(varchar,dl.FullDate,112)) as DateKey, dl.FullDate, datepart(dw,dl.FullDate) as DayNumberOfWeek, datename(weekday,dl.FullDate) as EnglishDayNameOfWeek, (Select top 1 SpanishDayNameOfWeek From DimDate Where EnglishDayNameOfWeek = datename(weekday,dl.FullDate)) as SpanishDayNameOfWeek, (Select top 1 FrenchDayNameOfWeek From DimDate Where EnglishDayNameOfWeek = datename(weekday,dl.FullDate)) as FrenchDayNameOfWeek, datepart(d,dl.FullDate) as DayNumberOfMonth, datepart(dy,dl.FullDate) as DayNumberOfYear, datepart(wk, dl.FUllDate) as WeekNumberOfYear, datename(MONTH,dl.FullDate) as EnglishMonthName, (Select top 1 SpanishMonthName From DimDate Where EnglishMonthName = datename(MONTH,dl.FullDate)) as SpanishMonthName, (Select top 1 FrenchMonthName From DimDate Where EnglishMonthName = datename(MONTH,dl.FullDate)) as FrenchMonthName, Month(dl.FullDate) as MonthNumberOfYear, datepart(qq, dl.FullDate) as CalendarQuarter, year(dl.FullDate) as CalendarYear, case datepart(qq, dl.FullDate) when 1 then 1 when 2 then 1 when 3 then 2 when 4 then 2 end as CalendarSemester, case datepart(qq, dl.FullDate) when 1 then 3 when 2 then 4 when 3 then 1 when 4 then 2 end as FiscalQuarter, case datepart(qq, dl.FullDate) when 1 then year(dl.FullDate) when 2 then year(dl.FullDate) when 3 then year(dl.FullDate) + 1 when 4 then year(dl.FullDate) + 1 end as FiscalYear, case datepart(qq, dl.FullDate) when 1 then 2 when 2 then 2 when 3 then 1 when 4 then 1 end as FiscalSemester from @datelist dl left join DimDate dd on dl.FullDate = dd.FullDateAlternateKey Where dd.FullDateAlternateKey is null COMMIT TRAN
DBA xPress features tools designed to make designing, maintaining, and administering your SQL Server databases easier than before. Now you can easily do things like visually navigate your databases, visualize schema dependencies, compare, synchronize, script, and navigate schemas, and more.
One of my personal favorite tools included with DBA xPress is the Data Inspector. Data Inspector allows you to create a comparison between the data stored in two different databases. Not only can you compare the data between databases, but you can also copy the data from one database to another.
With Data Inspector, there are three ways to sync data between databases. Data can either be synced directly between databases with the Data Inspector Synchronization Wizard,
a script can be generated to synchronize the data for you, or a hybrid of the first two options will directly sync the data and generate the script for you.
On a recent project I created a Date dimension script that I thought, I’d share (mostly so I would have access to it from anywhere). I based this script on one written by Mike Davis, but I included a few changes such as a first and last day of month flag, open flag, relative time period attributes, and a few others. Enjoy! Continue reading Create Date Dimension Table Script