[Code] Xtype definitions

'C' =  CHECK constraint 
'D' =  Default or DEFAULT constraint  
'F' =  FOREIGN KEY constraint  
'L' =  Log  
'FN' =  Scalar function  
'IF' =  In-lined table-function  
'P' =  Stored procedure  
'PK' =  PRIMARY KEY constraint (type is K)  
'RF' =  Replication filter stored procedure 
'S' =  System table  
'TF' =  Table function  
'TR' =  Trigger  
'U' =  User table  
'UQ' =  UNIQUE constraint (type is K)  
'V' =  View  
'X' =  Extended stored procedure 

[Ugly] Drop a view using Dynamic SQL

There's a time and a place for Dynamic SQL. This is, as far as I know, one of them.

I need to ensure a view (standard_view) is dropped in a particular database (My2008DB). That database is named for the year, so it's different each year. I can't use sp_msforeachdb for a particular (REDACTED) reason. You probably don't have that problem, I do.

So, best I can tell, the way to do it is to call sp_executesql from within the context of that other database. And since I have to execute the code to verify the drop before doing it, we wind up with this particularly ugly piece of code. Yes, we're 3 layers deep. sp_executesql calls My2008DB.dbo.sp_executesql, which calls the actual drop code. And yes, it works, at least on SQL Server 2005.

DECLARE @date SMALLDATETIME, @drop_view VARCHAR(8000) ,@SQLString nvarchar(500), @ParmDefinition nvarchar(500)
select @date = '5/15/2008'

--DROP VIEW.  Need to look in the DB, determined dynamically via the @date
SELECT @drop_view = 'if object_id(''My' + CONVERT(CHAR(4),@date, 112) + 'DB..standard_view'') is not null
    drop view standard_view'

--now we create a "wrapper" to run in the correct database context.
SELECT @sqlstring = N'execute My' + CONVERT(CHAR(4),@date,112) + 'DB.dbo.sp_executesql @mysql', @ParmDefinition = N'@mysql nvarchar(max)'

--execute in our dynamic db
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@mysql = @drop_view