Friday, August 31, 2012

Enforced Dependencies and Schemabinding

In trying to rename a table I encountered the dreaded error about enforced dependencies:
Object 'myTable' cannot be renamed because the object participates in enforced dependencies.

I first used the following script to identify the objects dependent upon the table:
SELECT DISTINCT o.object_id, o.name, o.type_desc, m.definition
FROM sys.sql_dependencies d
JOIN sys.objects o ON o.object_id = d.object_id
JOIN sys.objects r ON r.object_id = d.referenced_major_id
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE d.class = 1
AND r.name = 'myTable'

This yielded the names of three objects in my database which happen to use SCHEMABINDING in their definitions.

I didn't want to manually alter and recreate these objects, so instead I decided to create a script which will find the SQL code for each object, remove the WITH SCHEMABINDING syntax, drop the existing objects and then recreate them sans schema binding.

DECLARE
     @TheTable varchar(100),

     @Count int,
     @Max int,

     @object_id int,
     @name varchar(50),
     @type_desc varchar(50),
     @definition nvarchar(max),
 
     @DropSQL nvarchar(max),
     @CreateSQL nvarchar(max)

-- Remove schemabinding from objects tied to table.
DECLARE @Objects TABLE
(
     ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
     object_id int,
     name varchar(50),
     type_desc varchar(50),
     definition nvarchar(max)
)

-- Set the table name here:
SET @TheTable = 'MyTable'

INSERT INTO @Objects (object_id, name, type_desc, definition)
SELECT DISTINCT o.object_id, o.name, o.type_desc, m.definition
FROM sys.sql_dependencies d
JOIN sys.objects o ON o.object_id = d.object_id
JOIN sys.objects r ON r.object_id = d.referenced_major_id
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE d.class = 1
AND r.name = @TheTable

UPDATE @Objects
SET definition = 
     CASE
          WHEN definition LIKE '%SCHEMABINDING%' THEN REPLACE(definition, 'WITH SCHEMABINDING', '')
          ELSE definition
     END


SELECT @Count = 1, @Max = COUNT(ID) FROM @Objects

WHILE @Count <= @Max
BEGIN

     SELECT @object_id = object_id, @name = name, @type_desc = type_desc, @definition = definition
     FROM @Objects
     WHERE ID = @Count

     SET @DropSQL =   'DROP ' 
     +
     CASE @type_desc 
          WHEN 'VIEW' THEN 'VIEW'
          WHEN 'SQL_SCALAR_FUNCTION' THEN 'FUNCTION'
          WHEN 'SQL_TABLE_VALUED_FUNCTION' THEN 'FUNCTION'
          WHEN 'SQL_STORED_PROCEDURE' THEN 'PROCEDURE'
          WHEN 'SQL_TRIGGER' THEN 'TRIGGER'
     END
     +
     ' '
     + 
     @name

     PRINT @DropSQL
     EXEC sp_executeSQL @DropSQL

     SET @CreateSQL = @definition

     PRINT @CreateSQL
     EXEC sp_executeSQL @CreateSQL

     SET @Count = @Count + 1

END


Now I can incorporate this logic into a sproc and not have to worry about meddlesome constraints if I'm going to be renaming tables. The above script has been tested successfully under SQL 2008 and 2012, and will be able to automatically drop and recreate views, scalar and table valued functions, sprocs and triggers.






 

Monday, August 20, 2012

Those Bitches!

Classic scene I captured in animated GIF form in homage to the hilarious parody trailer, Star Wars Episode III: A Lost Hope.


Photoshop CS5 Slow Animated GIF Playback

In Photoshop CS5 you can import video and transform it into a series of layers, then export that collection of layers as an animated GIF.

However, one gotcha I encountered has to do with frame rate and playback speed of the resulting GIF animation. I found that despite the fact that I'd set the no delay between each frame, the animation appeared slow regardless of which browser I used.

To work around this annoyance, I found that I needed to tweak the frame rate of the animation in timeline mode. 

Here's how.

First off, make sure the QuickTime player is installed. Photoshop uses QuickTime to render video into layers, and the video must be viewable in QuickTime in order to be imported properly; if it's not and using some unrecognized video codec, it might import as a series of blank frames.

In Windows Explorer, right-click on the video file you want to import, and click the Details tab. Note the frame rate of the video in question is 25 frames per second (fps).




Click File => Import => Video Frames to Layers and import your video into Photoshop.

In Photoshop, perform edits you want, such as removing frames, adding text, whatever you need to do for your animation.

Ensure the Animation panel is visible by clicking Window and verifying a checkmark is beside Animation. Then, click on the button in the lower-right area of the frame view, which has a tooltip "Convert to timeline animation". Upon doing this, the frame view will disappear and be replaced with a timeline showing the layers stacked upon each other.



You should also notice that in the timeline view, the frame rate is shown. Note how it differs from the original frame rate of the imported video.



Now we alter the animation's frame rate to correspond to the original video's frame rate. In the upper-right corner of the Animation panel you should see a tiny upside-down triangle. Click this to reveal the Animation panel's popup menu, then click Document Settings.




Now the Document Timeline Settings window appears, from which you can change the Frame Rate from the default of 30 to the value corresponding to that of your imported video, in this case, 25.




Finally, click File => Save for Web & Devices to save the project as an animated GIF according to the settings you require. The resulting GIF should now be in sync with the original video in terms of frame rate, and render at about the same speed rather than annoyingly slow.