April 23, 2008 at 7:50 am
My question is:
How do I get the result of dynamic SQL into a variable?
karthik
April 23, 2008 at 8:33 am
Karthik
Use sp_executesql with an output parameter.
John
April 23, 2008 at 9:23 am
Hi All,
Below one is working fine.
--------------------------------------------------------
declare @sql varchar(1000), @minid int, @maxid int,
@table_n varchar(255), @col varchar(255), @dbnm varchar(50)
Create table #tmp1
(
ID int identity(1,1),
TableName varchar(50),
ColumnName varchar(50)
)
Create table #FinalResult
(
DBName varchar(15),
TableName varchar(50),
ColumnName varchar(50),
NULL_Row_Count int,
Total int
)
insert into #tmp1
select so.name, sc.name from syscolumns sc inner join sysobjects so on so.id = sc.id
where so.type = 'U'
select @dbnm = db_name()
select @minid = 1, @maxid = max(ID) from #tmp1
while (@minid <=@maxid)
begin
select @table_n = TableName,@col = ColumnName from #tmp1
where ID = @minid
select @sql = ' insert into #FinalResult(DBName,TableName,ColumnName,NULL_Row_Count,Total)'
select @sql = @sql + ' select ''' + @dbnm + ''', ''' + @table_n + ''', ''' +
@col + ''', (select count(*) from [' + @table_n + '] where ['+ @col + '] is null) '
select @sql = @sql + ' , (select count(*) from [' + @table_n +'])'
print @sql
exec ( @sql )
set @minid = @minid + 1
end
---------------------------------------------------------------------------
Select DBName,TableName,ColumnName,'Total_RowCount' = Total,NULL_Row_Count ,Non_NULL_Row_Count = Total - NULL_Row_Count
from #FinalResult
Thanks for all your people !
karthik
April 24, 2008 at 12:57 am
Can anybody help me to achieve the same task by using SET BASED THEORY ?
karthik
April 24, 2008 at 7:03 am
can we use master..spt_values table to avoid RBAR logic ?
karthik
April 24, 2008 at 9:40 am
karthikeyan (4/24/2008)
can we use master..spt_values table to avoid RBAR logic ?
I looks like CBAC logic to me.
Anyway, I've just knocked this up which should be a pretty efficient approach. It's not pretty (at all), has its limitations and could use more work, but it has it's uses...
if object_id('dbo.p_TableColumnSummary') is not null drop proc dbo.p_TableColumnSummary
go
create proc dbo.p_TableColumnSummary @TableName varchar(100) as
begin
declare @S varchar(8000)
select @S = isnull(@s, '') + 'count([' + name + ']) as [' + name + '], ' from syscolumns where object_name(id) = @TableName and not type in (34, 35, 99) order by colid
set @S = '
if object_id(''tempdb.dbo.#t1'') is not null drop table #t1
select ' + @S + 'count(*) as Count into #t1 from ' + @TableName
declare @S varchar(8000)
select @S = ''select ''''' + @TableName + ''''','' + cast(colid as varchar(10)) + '', '''''' + name + '''''', ['' + name + ''], Count - ['' + name + ''] from #t1 '' + + isnull(''union all '' + @S, '''') from tempdb.dbo.syscolumns where id = object_id(''tempdb.dbo.#t1'') and Name <> ''Count'' order by colid
if not right(rtrim(@s), 3) = ''#t1'' set @S = left(@s, len(@s) - patindex(''%1t#%'', reverse(@s))+2) + '' ''
select @S = ''select * from (select '''''''' as TableName, 0 as ColumnId, '''''''' as ColumnName, 0 as NumberOfNonNulls, 0 as NumberOfNulls) a where ColumnId = -1 union all '' + @S + ''order by ColumnId''
print @S
exec (@s)
'
print @S
exec (@s)
end
go
Use like this for a single table...
exec dbo.p_TableColumnSummary 'MyTable'
Or like this for a multiple tables...
if object_id('tempdb.dbo.#t2') is not null drop table #t2
create table #t2 (TableName sysname, ColumnId int, ColumnName sysname, NumberOfNonNulls int, NumberOfNulls int)
insert #t2 exec dbo.p_TableColumnSummary 'Table1'
insert #t2 exec dbo.p_TableColumnSummary 'Table2'
insert #t2 exec dbo.p_TableColumnSummary 'Table3'
select * from #t2
You will need to write TBAT logic to do it for your whole database, say.
Edit: Modified so won't error on large tables - it will just return what it can. This won't be a problem with varchar(max) in SQL 2005 - in SQL 2000 you could perhaps add a range for the colids and pass those as parameters.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 24, 2008 at 9:46 am
This will work for small databases in SQL 2000 (8000 characters is limiting here). Adapt to a loop for larger databases.
declare @S varchar(8000) --use varchar(max) in SQL 2005
select @S = isnull(@s, '') + 'insert #t2 exec dbo.p_TableColumnSummary ''' + name + '''' + char(10) from sysobjects where type in ('u', 'v') order by name
select @S = @S + ' select * from #t2'
print @S
if object_id('tempdb.dbo.#t2') is not null drop table #t2
create table #t2 (TableName sysname, ColumnId int, ColumnName sysname, NumberOfNonNulls int, NumberOfNulls int)
exec(@s)
Edit: Mis-pasted
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 25, 2008 at 1:46 am
Thanks a lot Ryan ! Really it opens my eyes broadly.
CBAC - Context Based Access Control which is related to Network protocols.
what do you mean by TBAT ? I am not sure just asking TBAT = TOW/Bushmaster Armored Turret (US). am i correct ? Can u explain it ?
karthik
April 25, 2008 at 2:31 am
karthikeyan (4/25/2008)
Thanks a lot Ryan ! Really it opens my eyes broadly.CBAC - Context Based Access Control which is related to Network protocols.
what do you mean by TBAT ? I am not sure just asking TBAT = TOW/Bushmaster Armored Turret (US). am i correct ? Can u explain it ?
Much simpler than that - compare CBAC and TBAT to RBAR. Rows, Columns and Tables. And yes, I did make them up 🙂
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply