Query to create dynamic Select statements for automation process.
The Query will create a query assuming data flows from a sql 2005 and up
or from sql 2005 and below. It cast the xml and varcha(max) to text fields.
Query to create dynamic Select statements for automation process.
The Query will create a query assuming data flows from a sql 2005 and up
or from sql 2005 and below. It cast the xml and varcha(max) to text fields.
/*******Create Dynamic Select Qry*********** ****Jorge L. Novo DBA********************/ /**** Variables*******/declare @TableName varchar(80) ---Name of table declare @Qtry varchar (8000) ----Final Qry Result declare @Column varchar (8000)----Columns of Qry declare @IsSql2000 int --- SQL 2005 to 2000 Indicator /***********Set Values *******/set @Qtry = 'Select ' set @TableName = 'TableNameHere' -----Table Name for Qry set @IsSql2000 = 1 ----SQL2000 Indicator 0 = moving data from sql2005 and above, 1= moving data from 2005 and below /****Lets the magic begin******/select c.name as ColumnName,c.colid,t.name as ctype,c.length as clength into #Qrty from syscolumns c inner join sysobjects o on c.id = o.id left outer join systypes t on t.xtype = c.xtype where o.id = object_id (@TableName) order by c.colid /****Now the real work begin ****/if @IsSql2000 = 1 begin select @Column = Coalesce(@column+',',' ')+ case when ctype = 'xml' then 'Cast(Cast( ['+ColumnName+'] as varchar(max))as text) as ['+ColumnName+']' else case when ctype ='varchar' and clength < 0 then 'Cast(['+ColumnName+'] as text)' else '['+ColumnName+'] as ['+ColumnName+']' end end from #Qrty end else begin select @Column = Coalesce(@column+',',' ')+ '['+ColumnName+']' from #Qrty end select @Qtry = @Qtry + @Column + ' From '+@TableName+' (Nolock)' select @Qtry drop table #Qrty print 'See U later alligator'