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')
