Pode parecer ridículo pensar que isso não é possível de ser feito da forma tradicional, mas não é. Quando você tenta importar um arquivo do Excel para o SQL Server ou SSIS, após selecionar o Data Source Microsoft Excel, mesmo que você forçe o Excel file path para um arquivo xlsx, a mensagem que aparece é:
File path contais a invalid Excel file. Please provide file with .xls extension.
Sinistro, mas tem solução, caso você não tenha o Excel 2007 para dar um Save As no formato 2003, que geralmente não pois o mais comum é você estar executando essa operação no servidor, faça da seguinte forma:
Mude o Data Source para Microsoft Office 12.0 Access Database Engine OLE DB Provider
Vá em Properties
Aponte para o arquivo xlsx desejado no campo Fonte de dados\Data source\Server os file name
Se nesse momento você clicar em Test Connection, ainda assim vai receber um erro de que o formato do bando de dados não é reconhecido. Sem pânico.
Vá até a aba Todas\All
Na linha Extended Properties. Clique duas vezes sobre o campo a defina o valor para “Excel 12.0”
Após isso, clique em Test Connection e veja que já é possível prosseguir com a instalação.
Como mais uma necessidade “doida” do dia a dia que vivemos, foi preciso procurar em todas as tabelas de um banco de dados SQL Server que continham uma determinada string. Pois bem, com várias opções em mãos, a menos complicada de enviar para um ambiente de produção sem a necessidade de um Application Server era uma query SQL que fizesse o trabalho.
Depois de muito suor e testes, o resultado foi a query abaixo:
DECLARE @SQLVARCHAR(8000)DECLARE @filtro VARCHAR(200)DECLARE @filtro_www VARCHAR(200)-- inicia a declaração do sqlSET @SQL=''SET @filtro ='%texto_procurado%'SELECT
tabelas.name AS Tabela
,colunas.name AS Coluna
,tipos.name AS Tipo
,colunas.length AS Tamanho
INTO
#result
FROM
sysobjects tabelas
INNERJOIN syscolumns colunas
ON colunas.id = tabelas.id
--INNERJOIN systypes tipos
ON tipos.xtype = colunas.xtype
WHERE
tabelas.xtype ='u'AND-- colocar aqui os tipos de coluna que serão buscados
tipos.name IN('text','ntext','varchar','nvarchar')-- cursor para varrer as tabelasDECLARE cTabelas cursor LOCAL fast_forward FORSELECTDISTINCT Tabela FROM #result
DECLARE @nomeTabela VARCHAR(255)OPEN cTabelas
fetch NEXTFROM cTabelas INTO @nomeTabela
while @@fetch_status =0BEGIN-- cursor para varrer as colunas da tabela correnteDECLARE cColunas cursor LOCAL fast_forward FORSELECT Coluna, Tipo, Tamanho FROM #result WHERE Tabela = @nomeTabela
DECLARE @nomeColuna VARCHAR(255)DECLARE @tipoColuna VARCHAR(255)DECLARE @tamanhoColuna VARCHAR(255)OPEN cColunas
-- monta as colunas da cláusula select
fetch NEXTFROM cColunas INTO @nomeColuna, @tipoColuna, @tamanhoColuna
while @@fetch_status =0BEGIN-- cria a declaração da variávelSET @SQL='declare @hasresults bit'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)-- cria o selectSET @SQL= @SQL+'select'+CHAR(13)+CHAR(10)SET @SQL= @SQL+CHAR(9)+''''+ @nomeTabela +''' AS NomeTabela'SET @SQL= @SQL+CHAR(9)+','+ @nomeColuna +CHAR(13)+CHAR(10)-- adiciona uma coluna com o tipo e o tamanho do campoSET @SQL= @SQL+CHAR(9)+','+''''+ @tipoColuna +''' AS '''+ @nomeColuna +'_Tipo'''+CHAR(13)+CHAR(10)SET @SQL= @SQL+CHAR(9)+','+'DATALENGTH('+ @nomeColuna +') AS '''+ @nomeColuna +'_Tamanho_Ocupado'''+CHAR(13)+CHAR(10)SET @SQL= @SQL+CHAR(9)+','+''''+ @tamanhoColuna +''' AS '''+ @nomeColuna +'_Tamanho_Maximo'''+CHAR(13)+CHAR(10)-- define a tabela temporária (#result)SET @SQL= @SQL+'into'+CHAR(13)+CHAR(10)+CHAR(9)+'#result_'+ @nomeTabela +CHAR(13)+CHAR(10)-- adiciona a cláusula fromSET @SQL= @SQL+'from'+CHAR(13)+CHAR(10)+CHAR(9)+ @nomeTabela +CHAR(13)+CHAR(10)-- inicia a montagem do whereSET @SQL= @SQL+'where'+CHAR(13)+CHAR(10)SET @SQL= @SQL+CHAR(9)+ @nomeColuna +' like '''+ @filtro +''''+CHAR(13)+CHAR(10)SET @SQL= @SQL+CHAR(13)+CHAR(10)+'select @hasresults = count(*) from #result_'+ @nomeTabela +CHAR(13)+CHAR(10)SET @SQL= @SQL+CHAR(13)+CHAR(10)+'if @hasresults > 0'SET @SQL= @SQL+CHAR(13)+CHAR(10)+'begin'SET @SQL= @SQL+CHAR(13)+CHAR(10)+CHAR(9)+'select * from #result_'+ @nomeTabela
SET @SQL= @SQL+CHAR(13)+CHAR(10)+'end'+CHAR(13)+CHAR(10)SET @SQL= @SQL+CHAR(13)+CHAR(10)+'drop table #result_'+ @nomeTabela
SET @SQL= @SQL+CHAR(13)+CHAR(10)
fetch NEXTFROM cColunas INTO @nomeColuna, @tipoColuna, @tamanhoColuna
-- descomente a linha abaixo para ver o SQL produzido no janela de Messages-- print @sqlEXEC(@SQL)SET @SQL=''END
close cColunas
deallocate cColunas
fetch NEXTFROM cTabelas INTO @nomeTabela
END
close cTabelas
deallocate cTabelas
DROPTABLE #result
declare @sql varchar(8000)
declare @filtro varchar(200)
declare @filtro_www varchar(200)
-- inicia a declaração do sql
set @sql = ''
set @filtro = '%texto_procurado%'
select
tabelas.name as Tabela
,colunas.name as Coluna
,tipos.name as Tipo
,colunas.length as Tamanho
into
#result
from
sysobjects tabelas
inner join syscolumns colunas
on colunas.id = tabelas.id
--
inner join systypes tipos
on tipos.xtype = colunas.xtype
where
tabelas.xtype = 'u'
and
-- colocar aqui os tipos de coluna que serão buscados
tipos.name in('text', 'ntext', 'varchar', 'nvarchar')
-- cursor para varrer as tabelas
declare cTabelas cursor local fast_forward for
select distinct Tabela from #result
declare @nomeTabela varchar(255)
open cTabelas
fetch next from cTabelas into @nomeTabela
while @@fetch_status = 0
begin
-- cursor para varrer as colunas da tabela corrente
declare cColunas cursor local fast_forward for
select Coluna, Tipo, Tamanho from #result where Tabela = @nomeTabela
declare @nomeColuna varchar(255)
declare @tipoColuna varchar(255)
declare @tamanhoColuna varchar(255)
open cColunas
-- monta as colunas da cláusula select
fetch next from cColunas into @nomeColuna, @tipoColuna, @tamanhoColuna
while @@fetch_status = 0
begin
-- cria a declaração da variável
set @sql = 'declare @hasresults bit' + char(13) + char(10) + char(13) + char(10)
-- cria o select
set @sql = @sql + 'select' + char(13) + char(10)
set @sql = @sql + char(9) + '''' + @nomeTabela + ''' AS NomeTabela'
set @sql = @sql + char(9) + ',' + @nomeColuna + char(13) + char(10)
-- adiciona uma coluna com o tipo e o tamanho do campo
set @sql = @sql + char(9) + ',' + '''' + @tipoColuna + ''' AS ''' + @nomeColuna + '_Tipo''' + char(13) + char(10)
set @sql = @sql + char(9) + ',' + 'DATALENGTH(' + @nomeColuna + ') AS ''' + @nomeColuna + '_Tamanho_Ocupado''' + char(13) + char(10)
set @sql = @sql + char(9) + ',' + '''' + @tamanhoColuna + ''' AS ''' + @nomeColuna + '_Tamanho_Maximo''' + char(13) + char(10)
-- define a tabela temporária (#result)
set @sql = @sql + 'into' + char(13) + char(10) + char(9) + '#result_' + @nomeTabela + char(13) + char(10)
-- adiciona a cláusula from
set @sql = @sql + 'from' + char(13) + char(10) + char(9) + @nomeTabela + char(13) + char(10)
-- inicia a montagem do where
set @sql = @sql + 'where' + char(13) + char(10)
set @sql = @sql + char(9) + @nomeColuna + ' like ''' + @filtro + '''' + char(13) + char(10)
set @sql = @sql + char(13) + char(10) + 'select @hasresults = count(*) from #result_' + @nomeTabela + char(13) + char(10)
set @sql = @sql + char(13) + char(10) + 'if @hasresults > 0'
set @sql = @sql + char(13) + char(10) + 'begin'
set @sql = @sql + char(13) + char(10) + char(9) + 'select * from #result_' + @nomeTabela
set @sql = @sql + char(13) + char(10) + 'end' + char(13) + char(10)
set @sql = @sql + char(13) + char(10) + 'drop table #result_' + @nomeTabela
set @sql = @sql + char(13) + char(10)
fetch next from cColunas into @nomeColuna, @tipoColuna, @tamanhoColuna
-- descomente a linha abaixo para ver o SQL produzido no janela de Messages
-- print @sql
exec(@sql)
set @sql = ''
end
close cColunas
deallocate cColunas
fetch next from cTabelas into @nomeTabela
end
close cTabelas
deallocate cTabelas
drop table #result
O que ela faz exatamente?
Busca no sysobjects todos as tabelas existentes no banco de dados.
Faz o mesmo na tabela syscolumns para obter o nome da coluna, o tamanho e o tipo delas, conforme configuração
Por fim, verifica se o texto existe na tabela e campo informado
Se houver, apresente um resultset completo com o nome da tabela, o dado, o tipo do campo e tamanho
A query foi testada no SQL Server 2000 e 2005, funcionando perfeitamente e ambos o casos.
É importante notar que não foram colocadas aqui considerações de desempenho ou o uso do recurso fulltext, sendo o objetivo principal resolver o problema de encontrar o texto desejado.
Apesar do SQL Management Studio Express ser uma ferramenta poderosa e gratuita, peca em algumas funcionalidades simples que a maioria das ferramentas free tem. A título de exemplo, as ferramentas do MySQL como o Administrator e o Query Browser são extremente completas, auxiliando inclusive em uma funcionalidade que coloque em questão aqui. A geração de scripts de dados de um banco.
Tá, conseguimos facilmente geraro script de criação das tabelas, procs, enfim, tudo mais de objetos existentes no banco através SQL Management Studio Express, clicando com o botão direto do mouse sobre o database na janela Object Explorer, indo em Tasks, Generate Scripts… Daí, basta selecionar os objetos a serem gerados e voilá! Está tudo aí, pronto para ser salvo e criado em qualquer outra servidor.
O problema são os dados. Tudo bem, dá até pra fazer um backup em arquivo binário, mas e se o servidor for de acesso restrito? A rotina de backup só funciona apontando para os discos existentes no computador instalado. Com algum esforço, daria para apontar para um diretório da rede, mas nem sempre isso está acessível.
Portanto, para auxiliar nesta tarefa, o colega Mark Clerget publicou um seu blog uma query muito bacana que é capaz de gerar os inserts dos dados das tabelas de um determinado banco. Dê uma olhada no script abaixo:
CREATETABLE #tmp (
SQLText VARCHAR(8000))CREATETABLE #tmp2 (
Id INTIDENTITY,
SQLText VARCHAR(8000))SET nocount ONDELETE #tmp
DELETE #tmp2
DECLARE @vsSQL VARCHAR(8000),
@vsCols VARCHAR(8000),
@vsTableName VARCHAR(40)DECLARE csrTables cursor FORSELECT name
FROM sysobjects
WHERETYPEIN('u')AND name IN('Customers')ORDERBY name
OPEN csrTables
fetch NEXTFROM csrTables INTO @vsTableName
while (@@fetch_status =0)BEGINSELECT @vsSQL ='',
@vsCols =''SELECT @vsSQL = @vsSQL +CASEWHEN sc.type IN(39,47,61,111)THEN'''''''''+'+'isnull(rtrim(replace('+ sc.name +','''''''','''''''''''')),'''')'+'+'''''',''+'WHEN sc.type =35THEN'''''''''+'+'isnull(rtrim(replace(substring('+ sc.name +',1,1000),'''''''','''''''''''')),'''')'+'+'''''',''+'ELSE'isnull(convert(varchar,'+ sc.name +'),''null'')+'',''+'ENDFROM syscolumns sc
WHERE sc.id = object_id(@vsTableName)ORDERBY ColID
SELECT @vsCols = @vsCols + sc.name +','FROM syscolumns sc
WHERE sc.id = object_id(@vsTableName)ORDERBY ColID
SELECT @vsSQL =SUBSTRING(@vsSQL,1,datalength(@vsSQL)-1)SELECT @vsCols =SUBSTRING(@vsCols,1,datalength(@vsCols)-1)INSERT #tmp
EXEC('select '+ @vsSQL +' from '+ @vsTableName)UPDATE #tmp
SET sqltext ='insert '+ @vsTableName +'('+ @vsCols +') values('+SUBSTRING(sqltext,1,datalength(sqltext)-1)+')'INSERT #tmp2
SELECT'DELETE from '+ @vsTableName
INSERT #tmp2 VALUES('GO')IF(SELECTCOUNT(id)FROM syscolumns WHERE id = object_id(@vsTableName)AND((STATUS & 128)=128))=1BEGININSERT #tmp2
SELECT'set identity_insert '+ @vsTableName +' on'ENDINSERT #tmp2
SELECT*FROM #tmp
IF(SELECTCOUNT(id)FROM syscolumns WHERE id = object_id(@vsTableName)AND((STATUS & 128)=128))=1BEGININSERT #tmp2
SELECT'set identity_insert '+ @vsTableName +' off'ENDINSERT #tmp2 VALUES('GO')INSERT #tmp2
SELECT'update statistics '+ @vsTableName
INSERT #tmp2 VALUES('GO')DELETE #tmp
fetch NEXTFROM csrTables INTO @vsTableName
END
close csrTables
deallocate csrTables
UPDATE #tmp2
SET sqltext =SUBSTRING(sqltext,1,charindex(',)',sqltext)-1)+',NULL)'WHERENOT(charindex(',)',sqltext)=0)UPDATE #tmp2
SET sqltext =REPLACE(sqltext,',''''',',null')WHERENOT(charindex(',''''',sqltext)=0)UPDATE #tmp2
SET sqltext =REPLACE(sqltext,'(''''',',null')WHERENOT(charindex('(''''',sqltext)=0)SET nocount off
SELECT sqltext FROM #tmp2 ORDERBY id
GODROPTABLE #tmp
DROPTABLE #tmp2
create table #tmp (
SQLText varchar(8000) )
create table #tmp2 (
Id int identity,
SQLText varchar(8000) )
set nocount on
delete #tmp
delete #tmp2
declare @vsSQL varchar(8000),
@vsCols varchar(8000),
@vsTableName varchar(40)
declare csrTables cursor for
select name
from sysobjects
where type in ('u')
and name in ('Customers')
order by name
open csrTables
fetch next from csrTables into @vsTableName
while (@@fetch_status = 0)
begin
select @vsSQL = '',
@vsCols = ''
select @vsSQL = @vsSQL +
CASE when sc.type in (39,47,61,111) then
'''''''''+' + 'isnull(rtrim(replace('+ sc.name + ','''''''','''''''''''')),'''')' + '+'''''',''+'
when sc.type = 35 then
'''''''''+' + 'isnull(rtrim(replace(substring('+ sc.name + ',1,1000),'''''''','''''''''''')),'''')' + '+'''''',''+'
else
'isnull(convert(varchar,' + sc.name + '),''null'')+'',''+'
end
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID
select @vsCols = @vsCols + sc.name + ','
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID
select @vsSQL = substring(@vsSQL,1,datalength(@vsSQL)-1)
select @vsCols = substring(@vsCols,1,datalength(@vsCols)-1)
insert #tmp
exec ('select ' + @vsSQL + ' from ' + @vsTableName)
update #tmp
set sqltext = 'insert ' + @vsTableName + '(' + @vsCols + ') values(' + substring(sqltext,1,datalength(sqltext)-1) + ')'
insert #tmp2
select 'DELETE from ' + @vsTableName
insert #tmp2 values ('GO')
if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' on'
end
insert #tmp2
select * from #tmp
if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' off'
end
insert #tmp2 values ('GO')
insert #tmp2
select 'update statistics ' + @vsTableName
insert #tmp2 values ('GO')
delete #tmp
fetch next from csrTables into @vsTableName
end
close csrTables
deallocate csrTables
update #tmp2
set sqltext = substring(sqltext,1,charindex(',)',sqltext)-1) + ',NULL)'
where not(charindex(',)',sqltext) = 0)
update #tmp2
set sqltext = replace(sqltext, ',''''',',null')
where not (charindex(',''''',sqltext) = 0)
update #tmp2
set sqltext = replace(sqltext, '(''''',',null')
where not (charindex('(''''',sqltext) = 0)
set nocount off
select sqltext from #tmp2 order by id
go
drop table #tmp
drop table #tmp2
Atentem à linha com o código “and name in ('Customers')” pois é este que faz o filtro nas tabelas contidas em sysobjects.. Se quiser apenas um subconjunto das tabelas nas quais terão o script gerado, basta alterar este filtro. O resultado da query acima executada sobre o banco de dados Northwind na tabela Region, pode ser visto na figura abaixo:
É um belo macete para as horas de aperto. Porém, este script não é infalível. Para alguns tipos de campos, como o image, ainda são gerados alguns erros. Mas como eles são excessão, é possível tratá-los de maneira separada. Caso alguém tenha alguma sugestão de melhoria, é avisar!
Bom, os leitores devem ter percebido que ultimamente tenho me aventurando a escrever algumas coisas sobre banco de dados. As experiências que tive em um projeto recente me levaram a ter que descobrir alguns macetes do SQL Server, os quais tratarei de publicar aqui na medida do possível.
Uma das necessidades da última aplicação era a constante troca de ambiente. Uma hora desenvolvimento, outra produção, outra qualidade, enfim.
Como o acesso era extramente restrito, não era algo trivial ficar “dropando” a base de dados, até porque nem era possível. O jeito era trabalhar direto no SQL para substituir algumas rotinas que a ferramenta e o acesso em nível administrativo facilitavam.
Por exemplo, para facilmente excluir todos os dados de todas a tabelas de um banco de dados SQL Server, a seguinte query resolve o problema:
-- desativa a integridade referencial
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO
-- reativa a integridade refencial
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
Em detalhes, o que a query faz: ela usa uma stored procedure não documentada do SQL Server chamada sp_MSForEachTable. Resumidamente, esta stored procedure dispara um comando SQL passado por parâmetro em todas as tabelas de um database, sendo o nome da tabela substituído pelo caracter “?”, como pode ser visto na query anterior. Se além de de apagar os dados das tabelas, quiser também reiniciar a contagem dos campos de chave primária, mais conhecido como RESEED, bastaria colocar a seguinte query antes da reativação da integridade referencial:
-- Reinicia o contador de chave primária de todas as tabelas
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 0)
'
GO
Na falta de ferramentas ou privilégios, esse tipo de macete é uma mão na roda.