Relacionando as colunas de cada tabela no SQL Server

Outras consultas que me pediram a aproximadamente um ano atrás e não publiquei no site, recuperam uma lista das colunas de cada tabela do banco de dados do SQL Server.

Basicamente, podemos utilizar a view INFORMATION_SCHEMA.COLUMNS, ou montar a nossa própria consulta com sys.columns:

SELECT s.name as [schema], t.name as [table], c.name AS [column]

FROM sys.schemas s
INNER JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]

INNER JOIN sys.columns c
ON t.[object_id] = c.[object_id]

Ou

SELECT TABLE_SCHEMA as [schema]
, TABLE_NAME as [table]
, COLUMN_NAME as [column]

FROM [INFORMATION_SCHEMA].[COLUMNS]

Exemplo de execução:

Para trazer a relação de todas as colunas de cada tabela em uma só linha, precisaremos de um pouco de criatividade com FOR XML e CROSS APPLY:

SELECT s.name as [schema], t.name as [table]

, SUBSTRING(c.[columns], 0, LEN(c.[columns])) AS [columns]

FROM sys.schemas s
INNER JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]

-- Relação de colunas [columns]
CROSS APPLY (
	SELECT (
		SELECT c.name + ', '
		FROM sys.columns c
		WHERE t.[object_id] = c.[object_id]
		FOR XML PATH('')
	) AS [columns]
) AS c

Exemplo de execução:

SQL Server 2012 “Denali” CTP 3 – Resumo

Para terminar a segunda série de artigos sobre o SQL Server Denali iniciada na semana passada, segue uma relação de artigos publicados sobre SQL Server 2012 Denali CTP 3 nestes últimos dias e um upgrade do resumo dos artigos que foram publicados desde o CTP 1.

Links gerais:

SQL Server Denali Wallpaper:
http://sqlblog.com/blogs/argenis_fernandez/archive/2011/06/09/sql-server-denali-wallpaper.aspx

Adventure Works for SQL Server Denali CTP3:
http://msftdbprodsamples.codeplex.com/releases/view/55330

e-book grátis do SQL Server 2012:
http://magalhaesv.wordpress.com/2011/10/13/e-book-gratis-do-sql-2012

Artigos em português:

Felipe Ferreira: SQL Server Denali CTP3:
http://blogs.solidq.com/fferreira/Post.aspx?ID=36

Antes DBCC SQLPERF(LOGSPACE), agora dm_db_log_space_usage
http://marcosfreccia.wordpress.com/2011/07/17/sql-server-denali-dm_db_log_space_usage

Novas funções: FORMAT e CONCAT
https://sqlfromhell.wordpress.com/2011/07/20/sql-server-denali-ctp3-novas-funcoes-format-e-concat

Novas funções: IIF e CHOOSE
https://sqlfromhell.wordpress.com/2011/07/19/sql-server-denali-ctp3-%e2%80%93-novas-funcoes-iif-e-choose

Novas funções de data: EOMONTH e DATETIMEFROMPARTS
https://sqlfromhell.wordpress.com/2011/07/18/sql-server-denali-ctp3-novas-funcoes-de-data

Novas funções: PARSE e TRY_CONVERT
https://sqlfromhell.wordpress.com/2011/07/15/sql-server-denali-ctp3-novas-funcoes-parse-e-try_convert

Funções Analíticas: FIRST_VALUE e LAST_VALUE
https://sqlfromhell.wordpress.com/2011/07/20/sql-server-denali-ctp3-funcoes-analiticas-first_value-e-last_value

Fabiano Neves Amorim: Funções Analíticas:
http://blogs.solidq.com/fabianosqlserver/Post.aspx?ID=58
http://blogs.solidq.com/fabianosqlserver/Post.aspx?ID=64

Problema: Serviço não inicia após a instalação
https://sqlfromhell.wordpress.com/2011/07/14/problemas-com-a-instalacao-do-denali-ctp-3-servico-nao-inicia

Artigos em francês:

Installation de SQL Server Denali CTP3 :
http://conseilit.wordpress.com/2011/07/12/installation-de-sql-server-denali-ctp3

Clause OVER étendue:
http://conseilit.wordpress.com/2011/07/15/denali-ctp3-clause-over-tendue

Comptes de service:
http://conseilit.wordpress.com/2011/07/12/denali-comptes-de-service

Artigos em inglês:

CTP3 is Here! Five Things to Know About the Next Version of SQL Server:
http://www.brentozar.com/archive/2011/07/five-things-sql-server-denali-ctp3

Policy-Based Management:
http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-denali-ctp3-policy-based-management

Tabular Model, Columnstore, new BIDS released!
http://apandre.wordpress.com/2011/07/14/tabular-model

SQL Server Project “Crescent” – Overview:
http://social.technet.microsoft.com/wiki/contents/articles/project-crescent-overview.aspx

SQL Server Project “Crescent” – Demo from WPC11:
http://blogs.technet.com/b/dataplatforminsider/archive/2011/07/18/new-sql-server-project-quot-crescent-quot-demo.aspx

SQL Server Columnstore Index FAQ:
http://social.technet.microsoft.com/wiki/contents/articles/sql-server-columnstore-index-faq.aspx

Restore improvements in SQL Server Denali CTP3 Management Studio:
http://blogs.msdn.com/b/wesleyb/archive/2011/07/18/restore-improvements-in-sql-server-denali-ctp3-management-studio.aspx

Artigos do SQL Server Denali CTP 1 em português:

Column Store – Felipe Ferreira:
http://blogs.solidq.com/fferreira/Post.aspx?ID=12&title=SQL+Denali+%e2%80%93+ColumnStore

Sequences:
https://sqlfromhell.wordpress.com/2010/11/10/sql-server-2011-ctp-1-sequences

Novidades do ORDER BY:
https://sqlfromhell.wordpress.com/2010/11/10/sql-server-2011-ctp-1-novidades-do-order-by

Contained Database e Usuários:
https://sqlfromhell.wordpress.com/2010/11/16/sql-server-2011-contained-database

Criando Server Roles:
https://sqlfromhell.wordpress.com/2010/11/17/sql-server-2011-server-roles

RESULT SETS:
https://sqlfromhell.wordpress.com/2010/11/22/sql-server-2011-result-sets-1
https://sqlfromhell.wordpress.com/2010/11/23/sql-server-2011-result-sets-2

SQL Server Denali CTP 1 – Resumo:
https://sqlfromhell.wordpress.com/2010/11/29/sql-server-2011-ctp-1-resumo

Fabrício Lima: SQL Server Denali CTP 1:
http://fabriciodba.wordpress.com/2010/11/14/sql-server-11-codinome-denali

Artigos do SQL Server Denali CTP 1 em inglês:

Full Text Search with Extended Properties – Bob Beauchemin:
http://www.sqlskills.com/BLOGS/BOBB/post/Using-SQL-Server-Denali-Full-Text-Search-With-Extended-Properties.aspx

Exploring THROW – Aaron Bertrand:
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/22/sql-server-v-next-denali-using-throw-instead-of-raiserror.aspx

New SSMS – Aaron Bertrand:
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/23/sql-server-11-denali-the-new-vs-shell.aspx

Change Startup Parameter – DBATAG:
http://sqlserver-training.com/sql-server-denali-changed-startup-parameter-new-feature

SQL Server 2012 “Denali” CTP3 – Funções Analíticas: FIRST_VALUE e LAST_VALUE

Surgiram ainda outras novas funções no SQL Server Denali CTP3, como a FIRST_VALUE() que simplifica cenários que necessitam recuperar o primeiro ou último valor de uma da coluna.

Após ler este artigo, dê uma olhada nas recomendações, onde relacionei artigos sobre funções analíticas de duas feras em SQL Server, Christophe LAPORTE (MCM) e Fabiano Neves Amorim (SolidQ).

Para os exemplos da função FIRST_VALUE(), vamos criar um cenário simples, com uma tabela com alguns registros com nomes e horários:

Antes da função FIRST_VALUE, era necessário alguma forma de sub consulta para ter em uma coluna o valor do primeiro registro:

Agora com o Denali, temos a função FIRST_VALUE para simplificar um pouco este cenário:

Assim como recuperar o último valor (penso que esta situação poderá ser substituída pela função LAST_VALUE nos próximos releases):

No caso de recuperar a primeira hora (HORA) de cada pessoa (NOME), antes do Denali tínhamos que usar uma clausula WHERE sobre a sub consulta:

Situação que pode ser facilmente simplificada com PARTITION BY:

Seria um problema da função LAST_VALUE()?

Em relação à função LAST_VALUE, ainda não descobri como ela funciona no SQL Server Denali, pois ao meu entendimento ela deveria trazer o último valor, no entanto, nos testes que realizei com ela não retornou nada além do valor corrente. Pelo que vi no artigo do Christophe LAPORTE, não sou o único que não se entendeu com esta função.

Recomendações:

Quem gostou das funções analíticas e quer conhecer um pouco mais sobre estas e outras funções que estão presentes no SQL Server Denali CTP 3, recomendo dar uma olhada nos seguintes artigos

Christophe LAPORTE: Denali CTP3 – Clause OVER étendue (artigo em francês)
http://conseilit.wordpress.com/2011/07/15/denali-ctp3-clause-over-tendue

Fabiano Neves Amorim: SQL Server – Windows Functions (artigos em português)
http://blogs.solidq.com/fabianosqlserver/Post.aspx?ID=58
http://blogs.solidq.com/fabianosqlserver/Post.aspx?ID=64

Referências:

Books Online for SQL Server “Denali”: Analytic Functions
http://msdn.microsoft.com/en-us/library/hh213234(v=SQL.110).aspx

SQL Server 2012 “Denali” CTP3 – Novas funções: FORMAT e CONCAT

Continuando a relação das novas funções que estão disponíveis no SQL Server Denali, temos a função FORMAT, que permite maior flexibilidade na formatação de dados e a função CONCAT que facilita questões de concatenação de dados para o formato de texto.

Função FORMAT

A função FORMAT, como as novas funções PARSE e TRY_PARSE tratadas no artigo anterior, é mais uma das que herdaram as questões de cultura da .NET Framework (ex.: pt-BR, en-US, fr-FR) e também pode ser uma alternativa para substituir a função CONVERT e CAST quem questões que exijam mais flexibilidade para formatação.

Para o primeiro exemplo, temos a flexibilidade de formatar valores numéricos e decimais, contando também com aspectos de cultura (opcional):

Outro recurso que poderá auxiliar muito quem trabalha com projetos internacionais, são as formatações monetárias:

E as formatações de data/hora baseada em alguma cultura ou a partir de um formato explícito:

Mais informações para entender como utilizar as expressões de formatação:
http://msdn.microsoft.com/en-us/library/26etazsy.aspx

Função CONCAT

Não vou ser hipócrita de dizer que a nova função CONCAT é uma daquelas funções que você não viveria sem, mas ainda sim é uma função que pode ser útil, além de evitar os tradicionais problemas com conversão de dados:

E ainda funciona bem sem ter problemas com a configuração CONCAT_NULL_YIELDS_NULL, que já tinha me dado alguns traumas com concatenação simples de texto (+), exemplo:

SQL Server 2012 “Denali” CTP3 – Novas funções: IIF e CHOOSE

Quando vi no “Books Online” as funções IIF e CHOOSE para o SQL Server Denali CTP3, pensei de imediato que finalmente será possível substituir alguns “CASE WHEN THEN END” para algo mais limpo!

Mas antes de pensar em IIF() e CHOOSE(), um lembrete extremamente importante: ISNULL(), NULLIF() e CASE WHEN não morreram no SQL Server Denali, Amen!

Função IIF

Para mostrar como o código ficou mais limpo com IIF, abaixo exemplos da utilização do CASE e a mesma lógica com IIF:

Mas isso não quer dizer que as expressões ficaram restritas com IIF, exemplo:

(Os exemplos com EXISTS e COLLATE ficaram bizarros…)

Função CHOOSE

Também temos exemplos de código mais limpo com o uso de CHOOSE ao invés do tradicional CASE:

Note que assim como na utilização do CASE, o CHOOSE retorna NULL quando não existe uma alternativa válida como resultado:

Mas como não contamos com um ELSE na função CHOOSE, temos que utilizar um ISNULL para retornar um valor padrão nas situações que não possuem alternativa válida:

Recomendação

Por fim, não comece a substituir CASE e as funções ISNULL, NULLIF por CHOOSE e IIF só porque “fica mais bonito”.

SQL Server 2012 “Denali” CTP3 – Novas funções de data

No SQL Server Denali CTP3 também foram lançadas algumas funções relacionadas à data e hora, sendo estas também bem úteis para o dia-a-dia dos DBAs e desenvolvedores.

Gerando dados do tipo DATE, TIME, SMALLDATETIME e DATETIME

O primeiro grupo de funções que apresentarei neste artigo são as funções DATEFORMPARTS, TIMEFROMPARTS, SMALLDATETIMEFROMPARTS e DATETIMEFROMPARTS, que permitem gerar dados do tipo DATE, TIME, SMALLDATETIME e DATETIME respectivamente com muita facilidade, exemplo:

Gerando DATETIME2 e DATETIMEOFFSET

No caso do uso de tipos de dados de data com muita precisão de milissegundos, teremos problemas com o DATETIME visto que ele não possui uma precisão tão boa, assim podemos utilizar DATETIME2 ou DATETIMEOFFSET, cujas precisões são configuráveis e precisas, por meio das funções DATETIME2FROMPARTS e DATETIMEOFFSETFROMPARTS respectivamente, exemplo:

Função EOMONTH

Também esta disponível a função EOMONTH ou “END OF MONTH” (fim do mês), que permite identificar o último dia do mês de uma data informada, exemplo:

Amanhã teremos mais um artigo sobre o SQL Server Denali CTP3!

SQL Server 2012 “Denali” CTP3 – Novas funções: PARSE e TRY_CONVERT

Dentre as funções que foram lançadas com este novo CTP do SQL Server Denali, temos algumas que realmente facilitam e muito o dia-a-dia dos DBAs e desenvolvedores, sendo as principais: PARSE, TRY_PARSE e TRY_CONVERT.

Função TRY_CONVERT

A partir de agora, ao invés de receber mensagens de erro como “The conversion of a varchar data type to a datetime data type resulted in an out-of-range value” ou ter que desenvolver suas próprias funções de CONVERT para situações que podem gerar erros, temos o TRY_CONVERT. O TRY_CONVERT permite converter valores da mesma forma que a função CONVERT, com o diferencial de não gerar erro quando não é possível recuperar um valor, exemplo:

Função PARSE

Também foram inseridas neste CTP as funções de PARSE, que permitem conversão de textos para algum tipo escolhido, também com a possibilidade de informar qual a cultura a ser utilizada, exemplo “pt-BR” (Brasil) e “en-US” (Inglês USA), utilizando nativamente CLR:

Função TRY_PARSE

Também o método PARSE possui sua variação TRY_PARSE, que retorna NULL quando não for possível a conversão:

Próxima semana, também apresentarei novas funcionalidades que estão disponíveis neste CTP!

Problema com o SQL Server 2012 “Denali” CTP 3 – Serviço não inicia após a instalação

Azarado que sou, instalei o SQL Server Denali CTP 3 ontem por duas vezes, mas o serviço do SQL Server não quis iniciar. Assim corri para o Event Viewer (Windows Logs\Application) e identifiquei as seguintes mensagens de erro na inicialização do SQL Server:

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘e:\sql11_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\modellog.ldf’. Diagnose and correct the operating system error, and retry the operation.

FCB::Open failed: Could not open file e:\sql11_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\model.mdf for file number 1. OS error: 3(The system cannot find the path specified.).

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘e:\sql11_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf’. Diagnose and correct the operating system error, and retry the operation.

FCB::Open failed: Could not open file e:\sql11_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1. OS error: 3(The system cannot find the path specified.).

Bom, já tinha visto algo parecido com este problema uma vez quando moveram os bancos de dados de sistema (msdb, model e tempdb) para outro disco, então resolvi tentar solucionar este problema da mesma forma.

Identifico onde foram parar os bancos de dados de sistema:

(Este é um print screen após ter sido ajustado, pois o tempdb ainda não tinha sido criado pelo SQL Server quando tive o problema).

No meu caso, eles estavam na pasta:

C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA

Abri o prompt de comando (CMD) como administrador e iniciei manualmente o serviço do SQL Server com “/f /T3608”, para ser possível ajustar os bancos de dados de sistema:

NET START MSSQL$ NomeDaInstancia /f /T3608

Agora, iniciar o prompt do SQL Server:

SQLCMD -S NomeDoComputado\NomeDaInstancia -E

Seguido dos comandos para ajustar os bancos de dados de sistema para pasta correta:

ALTER DATABASE model MODIFY FILE (NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\model.mdf')

ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\modellog.ldf')

ALTER DATABASE msdb MODIFY FILE (NAME = msdbdata, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\msdbdata.mdf')

ALTER DATABASE msdb MODIFY FILE (NAME = msdblog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\msdblog.ldf')

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\tempdb.mdf')

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\templog.ldf')

GO

Saí do SQLCMD (pelo comando EXIT), e parei o SQL Server:

NET STOP MSSQL$ NomeDaInstancia

Iniciei o serviço do SQL Server pelo Configuration Manager, e até ai, tudo ocorreu como esperado:

Ao tentar logar com no SQL Server Management Studio tive a seguinte mensagem:

Login failed for user ‘NomeDoComputador\NomeDoUsuario’. (Microsoft SQL Server, Error: 18456)

E a mesma resposta pelo SQLCMD:

Bom, então vou ter parar o SQL Server de novo, iniciar naquele modo emergencial e criar meu usuário como sysadmin:

NET STOP MSSQL$ NomeDaInstancia

NET START MSSQL$ NomeDaInstancia /f /T3608

Iniciar o prompt do SQL Server:

SQLCMD -S NomeDoComputado\NomeDaInstancia -E

Executar os seguintes comandos SQL:

CREATE LOGIN [NomeDoComputado\NomeDoUsuário] FROM WINDOWS;

GO

sp_addsrvrolemember 'NomeDoComputado\NomeDoUsuário', 'sysadmin'

GO

E parar o serviço do SQL Server:

NET STOP MSSQL$NomeDaInstancia

Novamente inicio o SQL Server pelo Configuration Manager, e feito isso, consegui me logar com o Management Studio sem maiores problemas:

Visto que se trata de uma versão de Preview, é até comum que ocorra problemas, mas fico no aguardo de uma solução.

SQL Server 2012 “Denali” CTP 1 – Resumo

E ai pessoas! Nestas últimas três semanas, eu pude conhecer algumas das novas funcionalidades da nova versão o SQL Server 2011 CTP 1 aka Denali, algumas pude escrever e outras que recomendei no Twitter.

Infelizmente não consegui avançar mais nesta atual versão, pois ainda desejava escrever sobre as novidades do Full Text Search e Spatial Data.

Mas fiquem no aguardo, pois “logo” teremos mais uma versão para testar. Enquanto isso, fiquem com uma relação de artigos sobre o SQL Server 2011 CTP 1:

Em português:

Column Store – Felipe Ferreira:
http://blogs.solidq.com/fferreira/Post.aspx?ID=12&title=SQL+Denali+%e2%80%93+ColumnStore
Obs.: Um ótimo artigo sobre a melhor novidade do SQL Server 2011 CTP 1.

Sequences:
https://sqlfromhell.wordpress.com/2010/11/10/sql-server-2011-ctp-1-sequences

Novidades do ORDER BY:
https://sqlfromhell.wordpress.com/2010/11/10/sql-server-2011-ctp-1-novidades-do-order-by

Contained Database e Usuários:
https://sqlfromhell.wordpress.com/2010/11/16/sql-server-2011-contained-database

Criando Server Roles:
https://sqlfromhell.wordpress.com/2010/11/17/sql-server-2011-server-roles

RESULT SETS:
https://sqlfromhell.wordpress.com/2010/11/22/sql-server-2011-result-sets-1
https://sqlfromhell.wordpress.com/2010/11/23/sql-server-2011-result-sets-2

Em inglês:

Full Text Search with Extended Properties – Bob Beauchemin:
http://www.sqlskills.com/BLOGS/BOBB/post/Using-SQL-Server-Denali-Full-Text-Search-With-Extended-Properties.aspx

Exploring THROW – Aaron Bertrand:
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/22/sql-server-v-next-denali-using-throw-instead-of-raiserror.aspx

New SSMS – Aaron Bertrand:
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/23/sql-server-11-denali-the-new-vs-shell.aspx

Change Startup Parameter – DBATAG:
http://sqlserver-training.com/sql-server-denali-changed-startup-parameter-new-feature

Agradecimentos:

Fabrício Lima, que esteve acompanhando as novidades sobre o SQL Server 2011:
http://fabriciodba.wordpress.com/2010/11/14/sql-server-11-codinome-denali

UPDATE: 21/07/2011

SQL Server Denali CTP 3:
https://sqlfromhell.wordpress.com/2011/07/21/sql-server-denali-ctp-3-resumo

SQL Server 2012 “Denali” CTP 1 – RESULT SETS – Parte 2

Bom dia pessoas,

Alguns recursos que poderão ajudar muito quem precisa de informações sobre os result sets de consultas ou stored procedures, são duas funções presentes no SQL Server 2011.

Com a primeira função, é possível capturar informações do result set de stored procedured:

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('uspGetEmployeeManagers'), 0)

E com a segunda, é possível obter informações do result set de até mesmo consultas:

DECLARE @CONSULTA VARCHAR(MAX)

SET @CONSULTA = 'SELECT object_id, name FROM master.sys.tables'

SELECT *
FROM sys.dm_exec_describe_first_result_set(@CONSULTA, NULL, 0)