April 09, 2014

Create Dynamic Select Statement

I have been very busy lately developing android applications as a hobby trying to get back to my Java school days, however, once in a while a build something that even though is look simple is great.
So, here are two scripts that can be used to extract data from a database or used to generate metadata for data extraction. 
Ah.. I still love anime :-)

CHEERS....



/**** Query to extract Create Select Statement from any SQLDB ****/
DECLARE @ModValue int 
DECLARE @ModController int 
DECLARE @Schema_Name varchar(80)
DECLARE @Exclude_Tables varchar(255)



Set @ModValue = 1 
Set @MODController = 0 
set @Schema_Name = 'Production'
set @Exclude_Tables= 'Culture';
Select  [TABLE_NAME],[QUERY] 
From( 
select 
ROW_NUMBER() Over (order by Table_Name) RowNum
,c2.TABLE_SCHEMA + '.' + c2.TABLE_NAME [TABLE_NAME]
,'SELECT ' + Substring( ( 
select ', '+ c1.TABLE_NAME + '.' + c1.COLUMN_NAME 
 
from 
INFORMATION_SCHEMA.COLUMNS c1 
where 
c2.TABLE_SCHEMA + '.' + c2.TABLE_NAME = c1.TABLE_SCHEMA + '.' + c1.TABLE_NAME  
order by c1.TABLE_NAME, c1.ORDINAL_POSITION  
for xml path('')), 2, 2147483647) + ' FROM ' + c2.TABLE_SCHEMA + '.' + c2.TABLE_NAME 'Query'  
from 
INFORMATION_SCHEMA.TABLES c2  where
( c2.TABLE_SCHEMA = @Schema_Name) And TABLE_NAME NOT IN (@Exclude_Tables)
)x 
Where 
(RowNum % @ModValue) = @ModController
go
/**************************************
Find special characters in database
*/
select 'select top 1 ''' + TABLE_NAME + ''' TAB, ''' + COLUMN_NAME + ''' COL from empower_wk.' + TABLE_NAME + ' where ' + COLUMN_NAME + ' like ''%''+char(13)+''%'' union all'
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_SCHEMA = 'person' and DATA_TYPE in ('varchar','char','nvarchar', 'nchar')

Contact Form

Name

Email *

Message *