Short Scripts – Junho – 2013

Pessoal, boa tarde.

Depois de alguns dias de molho devido a alguns probleminhas de saúde, estou voltando hoje as atividades.

E não poderia deixar de colocar a vida do meu blog em ordem.

Pois bem, você poderá encontrar na relação de hoje exemplos ou códigos relacionadas aos seguintes itens:

  • Concatenando Colunas na criação de Tabelas;
  • Criação de CTE;
  • Calculando Diferença entre Datas com DateDiff formatado;
  • Desabilitando Default Date Trace;

  • Encontrando a Depedência entre tabelas;

  • Matando processo em Sleeping;
  • Movendo o Banco TEMPDB para outras unidades ou pastas; e

  • Utilizando Pivot Table.

Segue abaixo a relação de Short Scripts disponibilizados para esta relação:

Exemplo 1 – Criando Pivot Table em conjunto com CTE:

Declare @pessoas as table (id int, nome varchar(30))
Declare @telefones as table (idpessoa int, tel varchar(9))

Insert into @pessoas values(1,’Marcelo’)
Insert into @pessoas values(2,’Camila’)
Insert into @pessoas values(3,’Tricolor’)

Insert into @telefones values(1,’123-1234′)
Insert into @telefones values(1,’222-2222′)
Insert into @telefones values(1,’333-3333′)

Insert into @telefones values(2,’123-1234′)
Insert into @telefones values(2,’4444-4234′)
Insert into @telefones values(2,’1406′)
Insert into @telefones values(3,’125487-12′)

;With CTE
as
(
select id,nome,tel, row_number() over (partition by id,nome order by id,nome ) as contador from @pessoas a inner join @telefones b ON a.id = b.idpessoa
)
select * from
(select id,nome,tel,contador from cte ) d pivot (max(tel) for contador in ([1],[2],[3])) as pvt

 

Exemplo 2 – Criando Common Table Expression com Hierarquia de Cargos:

CREATE TABLE tblCli (Codigo INT, Nome VARCHAR(40), CodigoSup INT)

INSERT INTO tblCli VALUES (1,’Presidente’, NULL)

INSERT INTO tblCli VALUES (2,’Superintendente’, 1)

INSERT INTO tblCli VALUES (3,’Gerente’, 2)

INSERT INTO tblCli VALUES (4,’Coordenador’, 3)

INSERT INTO tblCli VALUES (5,’Supervisor’, 4)

INSERT INTO tblCli VALUES (6,’Analista’, 5)

INSERT INTO tblCli VALUES (7,’Estagiário’, 6)

INSERT INTO tblCli VALUES (8,’Conselheiro’, 1)

WITH Rel

AS (

SELECT Nome, 1 AS Nivel, Codigo FROM tblCli

WHERE CodigoSup IS NULL

UNION ALL

SELECT tblCli.Nome, Nivel + 1, tblCli.Codigo FROM Rel

INNER JOIN tblCli ON Rel.Codigo = tblCli.CodigoSup

)

SELECT REPLICATE(‘–‘,Nivel) + ‘> ‘ + Nome FROM Rel

ORDER BY Nivel

 

Exemplo 3 – Criando Tabelas concatenando colunas:

Create Table #Tb_Teste

(codigo int identity(1,1),

codigogrupo int,

codigomodel int,

Controle As (Convert(VarChar(10),codigogrupo)+Convert(VarChar(10),CodigoModel)))
Insert Into #Tb_Teste Values(1,1)

 

Select * from #tb_teste

 

Exemplo – Calculando diferença entre Datas através da Função DateDiff com retorno Formatado:

— Exemplo 1 —
DECLARE @INICIAL AS DATETIME

DECLARE @FINAL AS DATETIME

 

SET @INICIAL = ‘2008-06-23 11:58:58’

SET @FINAL = ‘2008-06-25 12:23:12’
SELECT DATEDIFF(HH, CAST(‘1900-01-01’ AS DATETIME), @FINAL – @INICIAL) AS HORAS,

DATEPART(MINUTE, @FINAL – @INICIAL) AS MINUTOS,

DATEPART(SECOND, @FINAL – @INICIAL) AS SEGUNDOS

Go

 

— Exemplo 2 —
Declare @inicial datetime, @final datetime

 

Set @inicial = cast(‘2008-01-01 13:00:00’ as datetime)

Set @final = cast(‘2008-01-02 14:30:12’ as datetime)

 

Select Datediff(hour, @inicial, @final) as Horas,

Datediff(minute, @inicial, @final) – (datediff(hour, @inicial, @final) * 60) as Minutos,

datediff(second, @inicial, @final) – (datediff(minute, @inicial, @final) * 60) as Segundos

Go

 

Exemplo 5 – Desabilitando Default Trace File:

SP_Configure ‘Default Trace Enabled’, 0
Reconfigure With Override;

 

Exemplo 6 – Identificando a dependência entre Tabelas em conjunto com a Sys.Foreign_Keys:

— Exemplo 1 —

Select so.name As ‘Source Table’,
object_name(rkeyid) ‘Reference Table’,
object_name(constid) As ‘FK Name’,
object_name(sfk.parent_object_id) As ‘Column’
From sys.objects so Inner join sysforeignkeys sf
On so.object_id = sf.fkeyid
Inner Join sys.foreign_keys sfk
On so.object_id = sfk.referenced_object_id
Order By So.Name

 

— Exemplo 2 —

Select distinct sfk.name,
OBJECT_NAME(sfk.parent_object_id) As ‘Source Table’,
OBJECT_NAME(sfk.referenced_object_id) As ‘Foreign Table’,
(Select sc.name from sys.columns sc inner join sysconstraints sct
on sc.object_id = sct.id
and sc.column_id = sct.colid

Inner Join sys.foreign_keys sfk
On sct.id = sfk.referenced_object_id) As ‘Relationship Column’
from sys.foreign_keys sfk Inner Join sys.columns sc
On sfk.referenced_object_id = sc.object_id
Inner Join sysconstraints sct
On sct.id = sc.object_id
Where OBJECT_NAME(sfk.parent_object_id)=’Tabela2′

 

Exemplo 7 – Movendo o Banco TEMPDB para outra unidade ou pasta:

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(‘tempdb’);
GO

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘D:\MSSQL2008\TEMPDB\tempdb.mdf’);
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘D:\MSSQL2008\TEMPDB\templog.ldf’);
GO

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(‘tempdb’);
GO

 

Exemplo 8 – Matando todos os processos em Sleeping:

Declare @SPID SmallInt,
@Contador SmallInt,
@Comando VarChar(20)

Declare @Id Table
(CodId SmallInt)

Insert Into @ID
Select SPID from SysProcesses
Where Status=’sleeping’

Select Top 1 @SPID = CodID from @ID
Where CodID > 0

While @Contador >=(Select Count(*) from @ID)

Begin

Set @Comando=’Kill ‘+Convert(VarChar(2),@SPID)

Exec(@comando)

Set @Contador = @Contador – 1

Select Top 1 @SPID = CodID from @ID
Where CodID >@SPID
End

___________________________________________________________________________________________

 

Muito bem, estes são os Short Scripts disponíveis para esta relação.

Agradeço mais uma vez a sua visita.

Nos encontramos em breve.

Até mais.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

3 comentários em “Short Scripts – Junho – 2013”

  1. Bom dia!

    Muito bom todos os posts que vc publica sobre comandos, me ajudou bastante.
    Estou precisando de uma ajuda, preciso saber quais são as tabelas em meu banco de dados que tem relacionamento entre eles…. o total são 800 tabelas, preciso muito saber quais são as tabelas que estão relacionadas, saber a estrutura do banco, tentei fazer a engenharia reversa pelo dbdesigner e erwin, mas não consegui… o dbdesigner mostra todas as tabelas, mas não faz os relacionamentos, o erwin mostra as tabelas e relacionamentos, mas como estou com a versão trial só mostra 20 tabelas.

    Obrigado!

    Abs

    Curtir

    1. Bruno, boa tarde.

      Agradeço os seus comentários, espero estar ajudando mesmo.

      Segue abaixo um exemplo de código que poderá ajudar:

      – Exemplo 1 –
      select name As ‘FK Name’,
      Object_Name(parent_object_id) As ‘Table’,
      object_name(referenced_object_id) As ‘Column’
      from sys.foreign_keys

      – Exemplo 2-
      Select so.name As ‘Source Table’,
      object_name(rkeyid) ‘Reference Table’,
      object_name(constid) As ‘FK Name’,
      object_name(sfk.parent_object_id) As ‘Column’
      from sys.objects so Inner join sysforeignkeys sf
      On so.object_id = sf.fkeyid
      Inner Join sys.foreign_keys sfk
      On so.object_id = sfk.referenced_object_id
      Order By So.Name

      – Exemplo 3 –
      Select distinct sfk.name,
      OBJECT_NAME(sfk.parent_object_id) As ‘Source Table’,
      OBJECT_NAME(sfk.referenced_object_id) As ‘Foreign Table’,
      (Select sc.name from sys.columns sc inner join sysconstraints sct
      on sc.object_id = sct.id
      and sc.column_id = sct.colid
      Inner Join sys.foreign_keys sfk
      On sct.id = sfk.referenced_object_id) As ‘Relationship Column’
      from sys.foreign_keys sfk Inner Join sys.columns sc
      On sfk.referenced_object_id = sc.object_id
      Inner Join sysconstraints sct
      On sct.id = sc.object_id
      where OBJECT_NAME(sfk.parent_object_id)=’Tabela2′

      – Exemplo 4 –
      SELECT DB_NAME() AS dbname,
      o.type_desc AS referenced_object_type,
      d1.referenced_entity_name,
      d1.referenced_id,
      STUFF( (SELECT ‘, ‘ + OBJECT_NAME(d2.referencing_id)
      FROM sys.sql_expression_dependencies d2
      WHERE d2.referenced_id = d1.referenced_id
      ORDER BY OBJECT_NAME(d2.referencing_id)
      FOR XML PATH(”)), 1, 1, ”) AS dependent_objects_list
      FROM sys.sql_expression_dependencies d1 JOIN sys.objects o
      ON d1.referenced_id = o.[object_id]
      GROUP BY o.type_desc, d1.referenced_id, d1.referenced_entity_name
      ORDER BY o.type_desc, d1.referenced_entity_name

      – Exemplo 4 –
      WITH DepTree
      AS
      (
      SELECT DISTINCT o.name,
      o.[object_id] AS referenced_id ,
      o.name AS referenced_name,
      o.[object_id] AS referencing_id,
      o.name AS referencing_name,
      0 AS NestLevel
      FROM sys.objects o JOIN sys.columns c
      ON o.[object_id] = c.[object_id]
      WHERE o.is_ms_shipped = 0
      AND c.system_type_id IN (34, 99, 35) — TEXT, NTEXT and IMAGE

      UNION ALL

      SELECT r.name,
      d1.referenced_id,
      OBJECT_NAME(d1.referenced_id) ,
      d1.referencing_id,
      OBJECT_NAME( d1.referencing_id) ,
      NestLevel + 1
      FROM sys.sql_expression_dependencies d1
      JOIN DepTree r
      ON d1.referenced_id = r.referencing_id
      )
      SELECT name AS parent_object_name,
      referenced_id,
      referenced_name,
      referencing_id,
      referencing_name,
      NestLevel
      FROM DepTree t1 WHERE NestLevel > 0
      ORDER BY name, NestLevel

      – Exemplo 5 –
      WITH DepTree (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel)
      AS
      (
      SELECT o.[object_id] AS referenced_id ,
      o.name AS referenced_name,
      o.[object_id] AS referencing_id,
      o.name AS referencing_name,
      0 AS NestLevel
      FROM sys.objects o
      WHERE o.name = ‘t_demo_4’

      UNION ALL

      SELECT d1.referenced_id,
      OBJECT_NAME( d1.referenced_id) ,
      d1.referencing_id,
      OBJECT_NAME( d1.referencing_id) ,
      NestLevel + 1
      FROM sys.sql_expression_dependencies d1
      JOIN DepTree r ON d1.referenced_id = r.referencing_id
      )
      SELECT DISTINCT referenced_id, referenced_name, referencing_id, referencing_name, NestLevel
      FROM DepTree WHERE NestLevel > 0
      ORDER BY NestLevel, referencing_id;

      Curtir

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s