March 13, 2014 at 6:26 pm
below is my statement to get data from one column (VARCHAR) from table SUPPLY_ITEM_01
SELECT
@@SERVERNAME as ServerName,DB_NAME() AS DatabaseName,
SUM(CASE WHEN CHARINDEX (CHAR(013) , supplydetail) > 0 THEN 1 ELSE 0 END) AS TotalCHAR013,
SUM(CASE WHEN CHARINDEX (CHAR(012), supplydetail ) >0 THEN 1 ELSE 0 END)AS TotalCHAR012,
SUM(CASE WHEN CHARINDEX (CHAR(010), supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR010,
SUM(CASE WHEN CHARINDEX (CHAR(009),supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR009
FROM
QS_VRMart.dbo.SUPPLY_ITEM_01 ;
results are:
ServerName DatabaseName TotalCHAR013 TotalCHAR012 TotalCHAR010 TotalCHAR009
QS_PART QS_VRMart 246 0 42383 4538
I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.
thanks,
March 13, 2014 at 11:00 pm
It all depends on the application. I suggest to refer best practice naming convention and prepare the document. Also circulate the same to the development team before you proceed further.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 14, 2014 at 8:05 am
My query is based on one table and one column. I need to provide development team report to see which columns need to address in their application. I am having hard time to get from all the tables and all the VARCHAR columns.
thanks,
March 14, 2014 at 9:09 am
navie (3/14/2014)
My query is based on one table and one column. I need to provide development team report to see which columns need to address in their application. I am having hard time to get from all the tables and all the VARCHAR columns.thanks,
Hi and welcome to the forums. You have to realize that we can't see your screen and we have no idea what your system is like or what you are trying to do. So far all we know is that you posted a query against a table we know nothing about and you want to do something similar to that for all tables.
What exactly are you trying to do? Do all your tables have the same columns you are looking at? Are you trying to run a similar query for each table and only look at varchar columns?
I don't even know where to begin to help because I have no idea what the question is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 10:28 am
I am trying get count from all the columns in database which has bad data. the above example is based on one column and one table. I would like to get same result for all the columns have same kind data.
first I ran this query to get all the columns with varchar:
DECLARE @dtNow datetime = GETDATE()
SELECT
@@SERVERNAME as ServerName,DB_NAME() AS DatabaseName,sch.name AS SchemaName,
-- ta.object_id,
Object_Name(c.object_id) as TableName,c.name as ColumnName ,
tp.name 'Data type', c.max_length 'Max Length', @dtNow AS CaptureDateTime
from sys.tables ta
INNER JOIN sys.columns c ON ta.object_id = c.object_id
INNER JOIN sys.schemas sch ON ta.schema_id = sch.schema_id
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id
where
--c.object_id = '1556200594'
c.object_id = OBJECT_ID('[dbo].[STAR_ITEM_01]') and
tp.name = 'varchar'
and c.max_length > 10
AND C.NAME NOT LIKE 'RD%'
ORDER BY ta.name, c.name;
this gave me list of all the tables with their column with Varchar type. i need to put results from this with other query which is only returning for one column.
thanks
March 14, 2014 at 10:43 am
this will give me result that 240 rows has bad data as a TotalCHAR013
SELECT
SUM(CASE WHEN CHARINDEX (CHAR(013),BASEDESC) >0 THEN 1 ELSE 0 END) AS TotalCHAR013
FROM TD_MARKET.dbo.ITEM_01;
below will give me all the columns I need to scan to see if they have any bad data. now I need to make my above query may be part of below one. that's where I am having hard time to get total for each column.
DECLARE @dtNow datetime = GETDATE()
SELECT
@@SERVERNAME as ServerName,DB_NAME() AS DatabaseName,sch.name AS SchemaName,
-- ta.object_id,
Object_Name(c.object_id) as TableName,c.name as ColumnName ,
tp.name 'Data type', c.max_length 'Max Length', @dtNow AS CaptureDateTime
from sys.tables ta
INNER JOIN sys.columns c ON ta.object_id = c.object_id
INNER JOIN sys.schemas sch ON ta.schema_id = sch.schema_id
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id
where
--c.object_id = '1556200594'
c.object_id = OBJECT_ID('[dbo].[STAR_ITEM_01]') and
tp.name = 'varchar'
and c.max_length > 10
AND C.NAME NOT LIKE 'RD%'
ORDER BY ta.name, c.name
March 14, 2014 at 11:12 am
navie (3/13/2014)
...I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.
thanks,
I work with a data warehouse that has data partitioned into seperate schemas and tables, each with identical or similar column definition. Occasionally I want to run an one-off query across all the tables, so perhaps this is similar enough to your situation to be of help. What I'm doing here is leveraging INFORMATION_SCHEMA tables to dynamically construct SQL queries. In this case I'm also unionizing the result across multiple tables.
select 'select '''+TABLE_SCHEMA+''' ClientName, Claim_Period, count(*) row_count'
+char(13)+' from '+TABLE_SCHEMA+'.'+TABLE_NAME
+char(13)+' group by Claim_Period'
+char(13)+'UNION'
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like '%Claim%';
select 'ABC' ClientName, Claim_Period, count(*) row_count
from ABC.CLAIMS
group by Claim_Period
UNION
select 'EFG' ClientName, Claim_Period, count(*) row_count
from EFG.CLAIMS
group by Claim_Period
UNION
...
...
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 14, 2014 at 11:33 am
thanks, it didn't work. let me explain again. My first query gives me results fine for one column. but I need to achieve same goal for all the columns. that's were I am having hard time. 2nd query gives me all the column names. now how can merge first one to get results for all the columns. by the way, 2nd query is very handy to get all the info from database if some one needs to find column type.
BASEDESC is a column name and TD_Market.dbo.ITEM_01 is table name.
1.SELECT
SUM(CASE WHEN CHARINDEX (CHAR(013),BASEDESC) >0 THEN 1 ELSE 0 END) AS TotalCHAR013
FROM TD_Market.dbo.ITEM_01;
Results are:
TotalCHAR013
246
2. DECLARE @dtNow datetime = GETDATE()
SELECT
@@SERVERNAME as ServerName,DB_NAME() AS DatabaseName,sch.name AS SchemaName,
-- ta.object_id,
Object_Name(c.object_id) as TableName,c.name as ColumnName ,
tp.name 'Data type', c.max_length 'Max Length', @dtNow AS CaptureDateTime
from sys.tables ta
INNER JOIN sys.columns c ON ta.object_id = c.object_id
INNER JOIN sys.schemas sch ON ta.schema_id = sch.schema_id
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id
where
--c.object_id = '1556200594'
c.object_id = OBJECT_ID('[dbo].[STAR_ITEM_01]') and
tp.name = 'varchar'
and c.max_length > 10
AND C.NAME NOT LIKE 'RD%'
ORDER BY ta.name, c.name
Results are:
ServerNameDatabaseNameSchemaNameTableNameColumnNameData typeMax LengthCaptureDateTime
What I want to see in result is:
ServerNameDatabaseNameSchemaNameTableNameColumnNameData typeMax LengthTotalCHAR013 CaptureDateTime
Who I can achieve this goal:
March 14, 2014 at 1:24 pm
This might be what you are looking for:
DECLARE @dtNow datetime = GETDATE()
select newid() id
,@@SERVERNAME as ServerName
,DB_NAME() as DatabaseName
,sch.name as SchemaName
,Object_Name(c.object_id) as TableName
,c.name as ColumnName
,tp.name 'Data type'
,c.max_length 'Max Length'
,@dtNow as CaptureDateTime
,convert(bit, 0) [isProcessed]
,convert(int, 0) [TotalCHAR013]
into #t
from sys.tables ta
inner join sys.columns c on ta.object_id = c.object_id
inner join sys.schemas sch on ta.schema_id = sch.schema_id
inner join sys.types tp on c.user_type_id = tp.user_type_id
where tp.name = 'varchar'
and c.max_length > 10
and C.name not like 'RD%'
declare @id uniqueidentifier
declare @sql nvarchar(max)
declare @Columnname varchar(100)
declare @dbname varchar(100)
declare @schemaname varchar(100)
declare @TableName varchar(100)
while exists(select 1 from #t where isProcessed = 0)
begin
select top 1
@id = id
,@ColumnName = ColumnName
,@dbname = DatabaseName
,@schemaname = SchemaName
,@TableName = TableName
from #t where isProcessed = 0
set @sql =
'select
SUM(CASE WHEN CHARINDEX (CHAR(013), ' + @Columnname + ') >0 THEN 1 ELSE 0 END) AS TotalCHAR013
FROM ' + @dbname + '.' + @schemaname + '.' + @TableName
exec(@sql)
update #t set isProcessed = 1
where id = @id
end
select *
from #t
March 14, 2014 at 1:36 pm
I did not convert your select statement into an update. Use this instead:
set @sql =
'update #t set TotalCHAR013 =
(select SUM(CASE WHEN CHARINDEX (CHAR(013), ' + @Columnname + ') >0 THEN 1 ELSE 0 END)
FROM ' + @dbname + '.' + @schemaname + '.' + @TableName + ')'
+ ' where id = ' + '''' + convert(varchar(100), @id) + ''''
March 14, 2014 at 1:40 pm
thank you!
it is very close , but in results, it is only showing
TotalChar013. it 's not showing tablename, servername, column name etc.
if I get this, that will solve my problem.
thanks
March 14, 2014 at 2:00 pm
It's in the original table definition. Please post the sql you are using to get your result set?
March 14, 2014 at 2:01 pm
thank you very much. it work the way I want to see.
March 14, 2014 at 2:08 pm
one more thing. how I can add to get only results if they are not null or zero. in other words, populate table with results 1 or more.
thanks,
March 14, 2014 at 2:16 pm
You can either select what you need from the table:
select *
from #t
where TotalCHAR013 is not null and TotalCHAR013 > 0
or delete what you don't need and select all from the table:
delete #t
where TotalCHAR013 is null or TotalCHAR013 = 0
select *
from #t
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply