Thursday, October 24, 2013

Script to rename all tables Starting with "wd_" with script in SQL Server


-- using my tempdb as a sandbox...
USE tempdb
GO
CREATE TABLE wd_table01(x int)
CREATE TABLE wd_table02(x int)
CREATE TABLE wd_table03(x int)
GO
IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables;
SELECT TABLE_NAME AS tbl
INTO #tables
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE 'wd_%'
DECLARE @sql varchar(8000);
SELECT @sql=
(       SELECT 'exec sp_rename '''+tbl+''', '''+REPLACE(tbl,'wd_','')+''';'+CHAR(10)
        FROM #tables
        FOR XML PATH(''));
EXEC(@sql);

Wednesday, October 2, 2013

How do I create foreign Keyrelationship with a table in a different database?

You would need to manage the referential constraint across databases using a Trigger.
Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.
Example:
Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update
As
Begin

   If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN
      -- Handle the Referential Error Here
   END

END

Edited: Just to clarify. This is not the best approach with enforcing referential integrity. Ideally you would want both tables in the same db but if that is not possible. Then the above is a potential work around for you.

Credits:John Hartsock