Automatically add Foreign Keys

 
declare @code nvarchar(4000)
declare @template nvarchar(4000)
declare @PRIM_TABLE_SCHEMA sysname
declare @PRIM_TABLE_NAME sysname
declare @PRIM_COLUMN_NAME sysname
declare @CHILD_TABLE_SCHEMA sysname
declare @CHILD_TABLE_NAME sysname
declare @CHILD_COLUMN_NAME sysname
declare @rowid bigint
declare @maxrows bigint
declare @guid uniqueidentifier
 
set @template = 
'ALTER TABLE <<CHILD_TABLE_SCHEMA>>.<<CHILD_TABLE_NAME>> ADD CONSTRAINT
 FK_<<CHILD_TABLE_NAME>>_<<PRIM_TABLE_NAME>>_<<GUID>> FOREIGN KEY
 (
 <<CHILD_COLUMN_NAME>>
 ) REFERENCES <<PRIM_TABLE_SCHEMA>>.<<PRIM_TABLE_NAME>>
 (
 <<PRIM_COLUMN_NAME>>
 ) ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION'
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#primkeys') is not null
 drop table #primkeys
 
 
select distinct 
 col.TABLE_SCHEMA
 ,col.TABLE_NAME
 ,col.COLUMN_NAME
into #primkeys
from 
 INFORMATION_SCHEMA.TABLES t
 JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS con
  on con.TABLE_SCHEMA = t.TABLE_SCHEMA 
   and con.TABLE_NAME = t.TABLE_NAME 
            AND con.CONSTRAINT_TYPE = 'PRIMARY KEY'
 join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
  on con.CONSTRAINT_SCHEMA = u.CONSTRAINT_SCHEMA 
   and con.CONSTRAINT_NAME = u.CONSTRAINT_NAME 
 join INFORMATION_SCHEMA.COLUMNS col
  on u.TABLE_SCHEMA = col.TABLE_SCHEMA
   and u.TABLE_NAME = col.TABLE_NAME
   and u.COLUMN_NAME = col.COLUMN_NAME
where
 t.TABLE_TYPE = 'base table'
 and t.TABLE_NAME != 'sysdiagrams'
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#NonKeyColumns') is not null
 drop table #NonKeyColumns
 
 
select distinct 
 col.TABLE_SCHEMA
 ,col.TABLE_NAME
 ,col.COLUMN_NAME
into #NonKeyColumns
from 
 INFORMATION_SCHEMA.TABLES t
 join INFORMATION_SCHEMA.COLUMNS col
  on t.TABLE_SCHEMA = col.TABLE_SCHEMA
   and t.TABLE_NAME = col.TABLE_NAME
where
 t.TABLE_TYPE = 'base table'
 and t.TABLE_NAME != 'sysdiagrams'
 
except
 
select
 *
from
 #primkeys
 
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
if OBJECT_ID('tempdb..#colMappings') is not null
 drop table #colMappings
 
 
select
 ROW_NUMBER() over(order by p.TABLE_SCHEMA, p.TABLE_NAME, p.COLUMN_NAME,
  n.TABLE_SCHEMA, n.TABLE_NAME, n.COLUMN_NAME) as  RowID
 ,p.TABLE_SCHEMA as PRIM_TABLE_SCHEMA
 ,p.TABLE_NAME as PRIM_TABLE_NAME
 ,p.COLUMN_NAME as PRIM_COLUMN_NAME
 ,n.TABLE_SCHEMA as CHILD_TABLE_SCHEMA
 ,n.TABLE_NAME as CHILD_TABLE_NAME
 ,n.COLUMN_NAME as CHILD_COLUMN_NAME
into #colMappings
from
 #primkeys p
 join #NonKeyColumns n
  on (
   p.TABLE_SCHEMA != n.TABLE_SCHEMA
   or p.TABLE_NAME != n.TABLE_NAME
   )
   and p.COLUMN_NAME = n.COLUMN_NAME
 
 
 
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
-- *******************************************************************************************
select 
 @rowid = 1
 ,@maxrows = MAX(RowID)
from
 #colMappings
 
 
while (@rowid <= @maxrows)
begin
 
 select
  @PRIM_TABLE_SCHEMA = PRIM_TABLE_SCHEMA
  ,@PRIM_TABLE_NAME = PRIM_TABLE_NAME
  ,@PRIM_COLUMN_NAME = PRIM_COLUMN_NAME
  ,@CHILD_TABLE_SCHEMA = CHILD_TABLE_SCHEMA
  ,@CHILD_TABLE_NAME = CHILD_TABLE_NAME
  ,@CHILD_COLUMN_NAME = CHILD_COLUMN_NAME
  ,@guid = NEWID()
 from
  #colMappings m
 where
  m.RowID = @rowid
 
 
 set @code = replace(@template, '<<PRIM_TABLE_SCHEMA>>', @PRIM_TABLE_SCHEMA)
 set @code = replace(@code, '<<PRIM_TABLE_NAME>>', @PRIM_TABLE_NAME)
 set @code = replace(@code, '<<PRIM_COLUMN_NAME>>', @PRIM_COLUMN_NAME)
 set @code = replace(@code, '<<CHILD_TABLE_SCHEMA>>', @CHILD_TABLE_SCHEMA)
 set @code = replace(@code, '<<CHILD_TABLE_NAME>>', @CHILD_TABLE_NAME)
 set @code = replace(@code, '<<CHILD_COLUMN_NAME>>', @CHILD_COLUMN_NAME)
 set @code = replace(@code, '<<GUID>>', replace(@guid, '-', ''))
 
 
 print @code
 
 --exec sp_executesql @code
 
 set @rowid = @rowid +1
end
 
 

WatiN Web Test and Web Automation tool

WatiN is an awesome web test and web automation tool. I've been using it to automate the montly uploading of data to a site. This was the best IE solution I could find, and it is fantastic. Selenium & Firefox are another great tool combination. I really like Selenium but the coding of the web site wouldn't work with Firefox.

http://watin.sourceforge.net/


In case WatiN doesn't work with a particular dialog, be sure to check out the methods CanHandleDialog(Window window). WatiN did not originally work with FileUpload element of a particular web site. It worked with several other web site using various FileUpload elements. The solution was to modify the method FileUploadDialogHandler.CanHandleDialog(Window window).

  public override bool CanHandleDialog(Window window)
  {
            return (window.StyleInHex == "96CC20C4") || (window.StyleInHex == "96CC02C4")
                || (window.StyleInHex == "97CC02C4") || (window.StyleInHex == "97CC02C4");
  }

For some reason the style for the upload file dialog was different. I'm not sure if this was caused by a newer browser version, web site, or Windows 7. But once I added the new styles it worked fine.

In summary an awesome tool!