Select que retorna somente os campos preenchidos de uma tabela

[Kakaroto1309 0]

Alguem sabe como posso fazer um select para que traga somente as colunas preenchidas em uma tabela qualquer?

 

Por exemplo:

Tabela clientes possui 30 colunas (nome, cpf,...) porém 20 estão preenchidas.

Quero um select que verifica quais estão e traga na tela os dados.

 

Alguem sabe?

[advaldomesquita 93]

Kakaroto1309, possível é, mas teria que verificar somente nulos ou em branco tb? e qual o volume esperado?

Imagina uma tabela que tem 5000 linhas e uma delas tem os 30 campos, como voce trataria?

Eu fiz uma função que me retorna somente uma linha (campo chave pk obrigatorio).

[Kakaroto1309 0]

Kakaroto1309, possível é, mas teria que verificar somente nulos ou em branco tb? e qual o volume esperado?

Imagina uma tabela que tem 5000 linhas e uma delas tem os 30 campos, como voce trataria?

Eu fiz uma função que me retorna somente uma linha (campo chave pk obrigatorio).

 

No caso traria somente 1 registro (Top 1) de acordo com uma condição, por exemplo, que o campo ID for igual a 5, desde que serviria para qualquer tabela.

[advaldomesquita 93]

Abri minha função aqui e o contexto que tenho eh diferente do que você precisa. =\

No meu caso, eu converti linhas em colunas somente os campos que estavam preenchidos.

 

Eu acho que voce teria que trabalhar com a sys.objects e sys.columns para fazer esta verificacao, mas sem cursor e sim com um while. Teria como passar uma estrutura e o resultado esperado?

Abçs

[Kakaroto1309 0]

Abri minha função aqui e o contexto que tenho eh diferente do que você precisa. =\

No meu caso, eu converti linhas em colunas somente os campos que estavam preenchidos.

 

Eu acho que voce teria que trabalhar com a sys.objects e sys.columns para fazer esta verificacao, mas sem cursor e sim com um while. Teria como passar uma estrutura e o resultado esperado?

Abçs

 

Exemplo:

Tabela Cliente

Nome / CPF / Idade / Endereço / Número / Bairro / Cidade

Carlos / 111.111.111-11 / 20 anos / NULL ou Branco / NULL ou Branco / Moema / São Paulo

 

Ao relizar o select será retornado

Nome / CPF / Idade / Bairro / Cidade

Carlos / 111.111.111-11 / 20 anos / Moema / São Paulo

[advaldomesquita 93]

Estou tentando fazer algo no SQL2008 aqui, mas como meu ambiente eh restrito, vou ver se penso em algo

 

Pra você ter uma ideia do que estou tentando fazer:

1. a tabela por colunas e executar um select em cada uma delas;

2. se o resultado for NULL não aparece no select.

 

Até agora o esboço.

 

declare @id_tabela int = 1

if OBJECT_ID ('tempdb..#mytemp') is not null
drop table #mytemp

if OBJECT_ID ('tempdb..#exec') is not null
drop table #exec

if OBJECT_ID ('tempdb..#lin') is not null
drop table #lin


declare @campo varchar(max);
declare @cont int = 0;
declare @sql varchar(max)
declare @dados varchar(max) = ''


create table #exec (ident int identity (1,1), body varchar(max) , command varchar(max), campo varchar(max), id_serial int )

set rowcount 0 

SELECT B.NAME
into #mytemp
FROM SYS.OBJECTS A 
INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID 
WHERE A.NAME = 'TABELA' -- SUA TABELA

set rowcount 1 

select @campo = NAME from #mytemp

while @@rowcount <> 0 
begin 
    set rowcount 0 


insert into #exec (command, campo)
select 'select ' + @campo + ' from DATABASE.onwer.TABELA where id = 1', @campo -- SUA TABELA


delete from #mytemp  where NAME = @campo

set rowcount 1 

select @campo = NAME from #mytemp

end 



set rowcount 0 


update #exec set id_serial = @id_tabela

declare @table table (command varchar(max))
insert into @table values (' ')

declare @id char( 11 ) 
set rowcount 0 

select ident,body, command,  campo, id_serial into #lin from #exec 

set rowcount 1 


select @id = ident from #lin

while @@rowcount <> 0 
begin 
    set rowcount 0 

     select @sql = command from #lin where ident = @id

     update @table set command = command + @sql
     delete from #lin where ident = @id


    set rowcount 1 
    select @id = ident from #lin 
end 


set rowcount 0 

[Motta 645]

Exemplo:

Tabela Cliente

Nome / CPF / Idade / Endereço / Número / Bairro / Cidade

Carlos / 111.111.111-11 / 20 anos / NULL ou Branco / NULL ou Branco / Moema / São Paulo

 

Ao relizar o select será retornado

Nome / CPF / Idade / Bairro / Cidade

Carlos / 111.111.111-11 / 20 anos / Moema / São Paulo

 

Retornaria sempre uma linha somente ?

No caso de mais de uma linha os campos preenchidos poderiam ser outros !

 

Se a montagem da tela é de forma dinâmica uma solução é tratar se o conteúdo de cada campo (trazendo todos) é nulo ou branco e na aplicação exibi-lo ou não ficaria mais fácil.

[Kakaroto1309 0]

Retornaria sempre uma linha somente ?

No caso de mais de uma linha os campos preenchidos poderiam ser outros !

 

Se a montagem da tela é de forma dinâmica uma solução é tratar se o conteúdo de cada campo (trazendo todos) é nulo ou branco e na aplicação exibi-lo ou não ficaria mais fácil.

 

Sempre retornaria uma linha.

O duro é que não será usado em nenhuma página, será usado diretamente no SQL SERVER.

[Motta 645]

Qual o problema de mostrar campos em branco/nulo ?

[advaldomesquita 93]

O duro é que não será usado em nenhuma página, será usado diretamente no SQL SERVER. 

E precisa ser "tao" dinamico assim?

[advaldomesquita 93]

No meu ambiente eu fiz algo assim:

 

USE DATABASE
GO

if OBJECT_ID ('tempdb..#mytemp') is not null
drop table #mytemp

if OBJECT_ID ('tempdb..#exec') is not null
drop table #exec

if OBJECT_ID ('tempdb..#lin') is not null
drop table #lin

if OBJECT_ID ('tempdb..#variacao') is not null
drop table #variacao

if OBJECT_ID ('tempdb..#final') is not null
drop table #final

DECLARE @id_tabela VARCHAR(50) = 1 -- ID DA TABELA
DECLARE @SQL VARCHAR(MAX)
declare @sintaxe varchar(max)
declare @campo varchar(max)
declare @contador char( 11 ) 
declare @var varchar(max)
declare @max int

create table #exec (ident int identity (1,1), campo varchar(max) , command varchar(max), resultado varchar(max), id_serial int )
create table #variacao (command varchar(max), resultado varchar(max), id_serial varchar(max), campo varchar(max))

create table #final (ident int identity (1,1) , sintaxe varchar(max))

set rowcount 0 

SELECT 'SELECT ' + B.NAME + ' FROM BBA_MO..'+ A.NAME + ' WHERE ID_SERIAL = ' + @id_tabela + '' as sintaxe, b.name as campo
into #mytemp
FROM SYS.OBJECTS A 
INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID 
WHERE A.NAME = 'TABELA' --NOME DA TABELA


set rowcount 1 

select @sintaxe = sintaxe, @campo = campo from #mytemp


while @@rowcount <> 0 
begin 
    set rowcount 0 


insert into #exec (command, id_serial)
select @sintaxe, @id_tabela

update #exec set campo = @campo where command = @sintaxe 

delete from #mytemp  where sintaxe = @sintaxe

set rowcount 1 

select @sintaxe = sintaxe, @campo = campo from #mytemp
end 

set rowcount 0

set rowcount 1 


select @contador = ident, @campo = campo from #exec


while @@rowcount <> 0 
begin 
    set rowcount 0 

     select @sql = command from #exec where ident = @contador
  insert into #variacao(resultado) 
  exec (@sQL)

update #variacao set campo = (select campo from #exec where ident = @contador) where campo is null 
update #variacao set id_serial = (select id_serial from #exec where ident = @contador)


     delete from #exec where ident = @contador


    set rowcount 1 
    select @contador = ident, @campo = campo from #exec
end 


set rowcount 0 


-- SELECT ID_SERIAL, CAMPO, RESULTADO FROM #VARIACAO WHERE RESULTADO IS NOT NULL

set rowcount 1

select @campo = campo from #VARIACAO WHERE RESULTADO IS NOT NULL
set @SQL = 'select '

while @@rowcount <> 0 
begin 
   set rowcount 0 

set @SQL = @SQL + @campo + ' , '
insert into #final 
select (@SQL) 

delete from #variacao where campo = @campo

set rowcount 1 

select @campo = campo from #VARIACAO WHERE RESULTADO IS NOT NULL

end 
set rowcount 0 




set @SQL = @SQL + ' from DATABASE..TABELA where id = ' +@id_tabela
set @SQL  = REPLACE (@sql, ',  from',' from ') 

exec (@sql)

Pontos a se prestar atencao:

1. coloque as suas tabelas onde indicado;

2. não testei plano de execucao, entao verifique a performance;

3. Pf, avisa se funcionou, pq até eu fiquei curioso :P

 

Qq erro, por favor, volte a postar.

 

Abçs e boa sorte

[Osmarito 0]

Ainda, tenho essa mesma duvida...

Possuo uma tabela com 100 colunas e mais de 5.000.000 de linhas, sabendo que existem colunas completamente VAZIAS (não sei quais são) preciso fazer uma busca que me retorne, apenas as COLUNAS que possuem alguma informação, mesmo que seja em uma só linha....

Alguém pode me ajudar?

[advaldomesquita 93]

Ainda, tenho essa mesma duvida...

Possuo uma tabela com 100 colunas e mais de 5.000.000 de linhas, sabendo que existem colunas completamente VAZIAS (não sei quais são) preciso fazer uma busca que me retorne, apenas as COLUNAS que possuem alguma informação, mesmo que seja em uma só linha....

 

Alguém pode me ajudar?

 

Osmarito, tu tentou executar o ultimo script postado??

[Cosme Ferreira 7]

Boa tarde.

Espero poder ajudá-lo.

A tabela abaixo possue a função de auxiliar a composição do script e necessita ser criada.

tabAuxColumns => Recebe a estrutura de uma tabela passada como parametro na procedure mais abaixo.

CREATE TABLE [dbo].[tabAuxColumns](
	[sessao] [int] NOT NULL,
	[ID] [int] NOT NULL,
	[Name] [sysname] NULL,
	[DataType] [sysname] NULL,
	[Length] [int] NULL,
	[NumericPrecision] [int] NULL,
	[NumericScale] [int] NULL
) ON [PRIMARY]

tabCliente => Uma tabela qualquer para utilizar como exemplo. Poderia ser qualquer uma já existente em seu banco:

/****** Object:  Table [dbo].[tabCliente]    Script Date: 03/17/2015 12:38:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tabCliente](
	[codCliente] [int] IDENTITY(1,1) NOT NULL,
	[Nome] [varchar](255) NULL,
	[CPF] [varchar](20) NULL,
	[Idade] [int] NULL,
	[Endereco] [varchar](1000) NULL,
	[Numero] [varchar](20) NULL,
	[Bairro] [varchar](255) NULL,
	[Cidade] [varchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
	[codCliente] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

Insert na tabCliente para poder fazer simulações:

insert into tabCliente( Nome,
						CPF,
						Idade,
						Endereco,
						Numero,
						Bairro,
						Cidade)
values ('Carlos', --Nome
		 '111.111.111-11',
		 20,
		 null,
		 null,
		 'Moema',
		'São Paulo')

sprGetColumnWithValue => recebe como parametro o nome de uma tabela e caso necessário um filtro.

create procedure sprGetColumnWithValue
@_tableName varchar(255),
@_where     varchar(max)
as
BEGIN


set quoted_identifier off
set nocount on

--Delete caso exista algum registro para o mesmo numero de sessão
delete tabAuxColumns
where sessao = @@SPID

--Variável utilizada para compor queries
declare @query varchar(max)

--Busca informações para montar select dinamico
select @query =
" SELECT "+ltrim(rtrim(str(@@SPID)))+ ",
   ROW_NUMBER() OVER (ORDER BY clmns.column_id),
clmns.name AS [Name],
usrt.name AS [DataType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
CAST(clmns.precision AS int) AS [NumericPrecision],
CAST(clmns.scale AS int) AS [NumericScale]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
LEFT OUTER JOIN sys.schemas AS sclmns ON sclmns.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
WHERE
(tbl.name='" + @_tableName + "' and SCHEMA_NAME(tbl.schema_id)='dbo')
ORDER BY clmns.column_id ASC"


insert into tabAuxColumns
exec(@query)



declare @ID int = 1

declare @colName varchar(255),
@colType varchar(255),
@len int,
@Precision int,
@Scale int

declare @AuxTable varchar(255)
select @AuxTable = "##"+ @_tableName+ ltrim(rtrim(str(@@SPID)))

select @query = ("IF object_id('tempdb.."+ @AuxTable +"') IS NOT NULL
 begin
drop table "+ @AuxTable +"
 end ")

select @query = @query + 'CREATE TABLE '+ @AuxTable + '('

while exists(select 1 from tabAuxColumns where sessao = @@SPID and ID >= @ID)
begin
set @colName = null
set @colType = null
set @len = null
set @Precision = null
set @Scale = null

select @colName = Name,
@colType = DataType,
@len = [Length],
@Precision = NumericPrecision,
@Scale = NumericScale
from tabAuxColumns 
where sessao = @@SPID
 and ID = @ID

/****************************************************************************************/

if (@colType = 'int')
begin
select @query = @query + ' ' + @colName  + ' int '
end
else if (@colType = 'varchar')
begin
select @query = @query + ' ' + @colName + ' varchar( ' + ltrim(rtrim(STR(@len))) + ')'
end
else if (@colType = 'bit')
begin
select @query = @query + ' ' + @colName + ' bit'
end
else
if (@colType = 'decimal')
begin
select @query = @query + ' ' + @colName + ' decimal(' + ltrim(rtrim(STR(@Precision))) + ',' + ltrim(rtrim(STR(@Scale))) + ')'
end
else if (@colType = 'datetime')
begin
select @query = @query + ' ' + @colName + ' datetime'
end
else if (@colType = 'char')
begin
select @query = @query + ' ' + @colName + ' char( ' + ltrim(rtrim(STR(@len))) + ')'
end


if exists(select 1 from tabAuxColumns where sessao = @@SPID and ID = @ID + 1)
begin
select @query = @query + ','
end


-- /****************************************************************************************/

select @ID = @ID + 1
end


select @query = @query + ')'

exec(@query)


set @ID = 1

set @query = "insert into " + @AuxTable + "("

while exists(select 1 from tabAuxColumns where sessao = @@SPID and ID >= @ID)
begin
set @colName = null
set @colType = null
set @len = null
set @Precision = null
set @Scale = null

select @colName = Name,
@colType = DataType,
@len = [Length],
@Precision = NumericPrecision,
@Scale = NumericScale
from tabAuxColumns 
where sessao = @@SPID
 and ID = @ID

/****************************************************************************************/


select @query = @query + ' ' + @colName

if exists(select 1 from tabAuxColumns where sessao = @@SPID and ID = @ID + 1)
begin
select @query = @query + ','
end


-- /****************************************************************************************/

select @ID = @ID + 1
end

set @query = @query + ")select * from " + @_tableName

if isnull(@_where,'') <> ''
begin
select @query = @query + " where " + @_where
end

exec(@query)

declare @auxRetorno table (retorno int)

set @query = "select count(*) from " + @AuxTable

insert into @auxRetorno
exec(@query)

if(select retorno from @auxRetorno) = 0
begin
raiserror('Não existe registros para retornar',15,15)
RETURN
end

declare @queryRetorno varchar(max)

select @queryRetorno = ""

set @ID = 1
while exists(select 1 from tabAuxColumns where sessao = @@SPID and ID >= @ID)
begin
set @colName = null

delete from @auxRetorno

select @colName = Name
from tabAuxColumns 
where sessao = @@SPID
 and ID = @ID

/****************************************************************************************/


select @query = "select count(*) from " + @AuxTable + " where isnull(" + @colName + ",'0') <> '0'"

insert into @auxRetorno
exec(@query)

if(select retorno from @auxRetorno) > 0
begin
if(@queryRetorno = "")
begin
select @queryRetorno = "select " + @colName
end
else
begin
select @queryRetorno = @queryRetorno + "," + @colName
end
end



-- /****************************************************************************************/

select @ID = @ID + 1
end

select @queryRetorno = @queryRetorno + " from " + @AuxTable
exec(@queryRetorno)

END

Para utilizar basta chamar a procedure passando os parametros @_tableName e se necessário o @_where. Ex:

exec sprGetColumnWithValue 'tabCliente', ''

ou, caso precise filtrar os dados na tabela:

exec sprGetColumnWithValue 'tabCliente', 'codCliente = 1'

A quantidade de registros é irrelevante, pois o retorno é montado dinamicamente levando em consideração se existe pelo menos um registro com valor para cada coluna e, caso exista, é retornada a coluna.

Fico à disposição.

[Ric_calheiros 0]

Boa tarde a todos....

Cosme Ferreira eu tentei utilizar esta procedure que você nos presentou. Porém estou travado em 3 erros:

 

1º  -  Mensagem 103, Nível 15, Estado 4, Procedimento sprGetColumnWithValue, Linha 24
A identificador iniciada com ',
   ROW_NUMBER() OVER (ORDER BY clmns.column_id),
clmns.name AS [Name],
usrt.name AS [DataType],
CAST(CASE WHEN baset.name ' é muito extensa. O comprimento máximo é 128.

 

2º  -  Mensagem 1038, Nível 15, Estado 4, Procedimento sprGetColumnWithValue, Linha 195
Um nome de objeto ou coluna está ausente ou está vazio. Verifique se as colunas das instruções SELECT INTO têm nomes. Nas outras instruções, procure nomes de alias vazios. Aliases definidos como "" ou [] não são permitidos. Altere o alias para um nome válido.

 

3º  -  Mensagem 1038, Nível 15, Estado 4, Procedimento sprGetColumnWithValue, Linha 219
Um nome de objeto ou coluna está ausente ou está vazio. Verifique se as colunas das instruções SELECT INTO têm nomes. Nas outras instruções, procure nomes de alias vazios. Aliases definidos como "" ou [] não são permitidos. Altere o alias para um nome válido.

 

Seria possível me ajudar???

[M.O. Moura 0]

Boa tarde! Também tentei executar a procedure do Cosme e deu o mesmo erro:

Msg 103, Level 15, State 4, Procedure sprGetColumnWithValue, Line 20
The identifier that starts with ',
   ROW_NUMBER() OVER (ORDER BY clmns.column_id),
clmns.name AS [Name],
usrt.name AS [DataType],
CAST(CASE WHEN baset.name ' is too long. Maximum length is 128.
Msg 1038, Level 15, State 4, Procedure sprGetColumnWithValue, Line 191
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Procedure sprGetColumnWithValue, Line 215
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
 

 

Alguém poderia dar um help??