SQL - SERVER - Base de Dados

Click em Linguagem para conhecer melhor a sintaxe dos comandos
Attach base de dados
-- Finalizar o Sql-Server ( stop )
-- Copiar os arquivos database.MDF e database_LOG.LDF da 
-- máquina atual para a pasta <DADOS> da máquina <SERVER>
-- No Query Analizer da máquina <SERVER>, se logar como sa e executar a seguinte procedure:

 Exec sp_attach_db 'cartao1',
'D:\TPPRODUCAO\SQL_DADO\cartao1\cartao1_Data.MDF',
'D:\TPPRODUCAO\SQL_DADO\cartao1\cartao1_Log.LDF'

Restore base de dados
RESTORE DATABASE CARTAO1 FROM DISK = 'D:\TPPRODUCAO\SQL_DADO\cartao1\cartao1_20131029_023439.BAK' 
WITH REPLACE, MOVE 'CARTAO1_Data' TO
'D:\TPPRODUCAO\SQL_DADO\cartao1\cartao1_Data.MDF', 
MOVE 'CARTAO1_Log' TO 
'D:\TPPRODUCAO\SQL_DADO\cartao1\cartao1_Log.LDF'

 Cria usuario
No Query Analizer ( logado como SA ) na máquina <SERVER> execute as seguintes instruções:

EXEC sp_addlogin 'usrcartao1','xxxzzz', 'cartao1'
GO
-- coloca o atributo de administrador da base de dados
EXEC master..sp_addsrvrolemember @loginame = 'usrcartao1', @rolename = N'sysadmin'
GO
USE cartao1
GO
EXEC sp_changedbowner 'usrcartao1'
GO

 -- Redução do log, rode 
backup log cartao1 with truncate_only

 -- entre no Interprise Manager
-- click direito no database e selecione "all task"
-- selecione "Shrind Database".
-- click em "OK"

Exportar uma tabela
Set Server=NOME_SERVIDOR 
Set DbUser=usr_base 
Set DbPwd=usr_senha 
Bcp tabela OUT D:\bk\tabela .bcp -o D:\bk\tabela .log -S%Server% -U%DbUser% -P%DbPwd% -c
>>> salvar em exporta.bat
Importar uma tabela
Set Server=NOME_SERVIDOR 
Set DbUser=usr_base 
Set DbPwd=usr_senha 
Bcp tabela IN D:\bk\tabela .bcp -o D:\bk\tabela .log -S%Server% -U%DbUser% -P%DbPwd% -c
>>>> salvar em importa.bat
Select para arquivo texto
BCP "SELECT TOP 10 NSUCREDITO FROM cartao1..LANCREDITO" queryout C:\TT.txt -c -E -Uxxx -Pzzz -SVSEGER01\VSEGER01

criar tabele tmp
SELECT * INTO TMP_VVPF FROM VVPF WHERE DTMODI='@@'

criar LINK entre bases 
-- CRIAR UM LINK ENTRE SERVIDORES SLQSERVER -
-- use o query analyzer e login como SA
-- @server = N'FC', (nome do link)
-- @srvproduct=N'vLinkFC', (descricao do link)
-- @provider=N'SQLOLEDB', (dll a ser utlizada)
-- @datasrc=N'10.1.1.18' (ip do servidor remoto)  
EXEC master.dbo.sp_addlinkedserver
@server = N'FC',
@srvproduct=N'vLinkFamilyCard',
@provider=N'SQLOLEDB',
@datasrc=N'10.1.1.18'   

-- CRIAR O LOGIN 
-- @rmtsrvname=N'FC', (nome o link)
-- @useself=N'True', (se varios usuarios pode usar o link)
-- @locallogin=N'usr_base', ( usuario do servidor local )
-- @rmtuser=N'usr_base1', (usuario do servidor remoto)
-- @rmtpassword='usr_senha' (senha do servidor remoto)

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'FC',
@useself=N'True', 
@locallogin=N'usr_base',
@rmtuser=N'usr_base1',
@rmtpassword='usr_senha'

 manutenção de tabelas
Executar um comando externo a partir de uma procedure
EXEC MASTER..XP_CMDSHELL 'del D:\ABC.SQL'
INSERT TAB_local A PARTIR DE TAB_remota ATRAVÉS DO LINK
INSERT INTO TAB1_local SELECT * FROM FC.nomebase.dbo.TAB1_remoto RM WHERE RM.COL1 NOT IN (SELECT COL1 FROM TAB1_local)
UPDATE EM TAB_local COM DADO TAB_remota ATRAVÉS DO LINK
UPDATE TAB1 SET TAB1.COL1 = RM.COL2
FROM TAB1 INNER JOIN  FC.nomebase.dbo.TAB2 RM ON RM.COL3 = TAB1.COL3
ADICIONA COLUNA
ALTER TABLE TAB1 add COLUNA VARCHAR(10) NULL
APAGA COLUNA TABELA
ALTER TABLE TAB1 DROP COLUMN COL1
ALTERA TAMANHO DA COLUNA
ALTER TABLE TAB1 ALTER COLUMN COL1 VARCHAR(35) NULL
UPDATE COLUNA
 UPDATE TAB1 SET COLUNA ='N' WHERE COLUNA='S'
UPDATE COLUNA TAB1 COM SELECT TAB2
UPDATE TAB1 SET
        COL2 = T2.COL2,
        COL3 = T2.COL3
FROM TAB1 T1
        INNER JOIN TAB2 T2 ON T2.COL1 = T1.COL1
WHERE
        T1.COL1 IN (21, 31)
 INSERT A PARTIR DE OUTRA TABELA
INSERT INTO TAB1 SELECT * FROM TAB2 WHERE COL1 NOT IN (SELECT COL1 FROM TAB1)
ou
INSERT INTO TAB1 (COL1,COL2,COL3)
      SELECT 
           TB2.COL1,TB2.COL2,TB2.COL3
      FROM 
           TAB2 TB2
      WHERE 
           TB2.COL1 = XPTO
CRIAR UMA TABELA
CREATE TABLE NomeTabela 
 (
       CNPJ_ADM                char(14) NOT NULL,
       MESES                     int NULL,
       LIMITE_CPF              float NULL,
       DTFECHA varchar(10) NULL,
       CNPJ_SACADO          char(14) NULL
)
go
APAGA TABELA
DROP TABLE NomeTabela
CRIA ÍNDICE DA TABELA 
 CREATE INDEX NomeIndex ON NomeTabela 
(
       CPFBOLETO
)
go
APAGA ÍNDICE DA TABELA
DROP INDEX NomeTabela.NomeIndex
CRIA UMA CONSTRAINT
ALTER TABLE NomeTabelaQueRecebeChaveEstrangeira
       ADD CONSTRAINT NomeConstraint
              FOREIGN KEY (NomeColunaTabelaOrigem )
                             REFERENCES NomeTabelaOrigem
APAGA UMA CONSTRAINT
ALTER  TABLE NomeTabela
DROP CONSTRAINT NomeConstraint
CHECA SE EXISTE TABELA TEMPORARIA
IF OBJECT_ID('TEMPDB..#TMP_TABELA') IS NOT NULL
    DROP TABLE #TMP_TABELA

TIPOS DE DADOS
SQL ServerData Precision
BITInteger: 0 or 1
TINYINTPositive Integer 0 -> 255
SMALLINTSigned Integer -32,768 -> 32,767
INTSigned Integer -2^31 -> 2^31-1
BIGINTSigned Integer -2^63 -> 2^63-1
REALFloating precision -1.79E + 308 -> 1.79E + 308
FLOATFloating precision -3.40E + 38 -> 3.40E + 38
MONEY4 decimal places, -2^63/10000 -> 2^63-1/10000
SMALLMONEY4 decimal places, -214,748.3648 -> 214,748.3647
DECIMALFixed precision -10^38 + 1 -> 10^38 - 1
NUMERICFixed precision -10^38 + 1 -> 10^38 - 1
DATETIMEDate+Time 1753-01-01 -> 9999-12-31, accuracy of 3.33 ms
SMALLDATETIMEDate+Time 1900-01-01 -> 2079-06-06, accuracy of one minute
CHARnFixed-length non-Unicode string to 8,000 characters
NCHARnFixed-length Unicode string to 4,000 characters
VARCHARnVariable-length non-Unicode string to 8,000 characters
NVARCHARnVariable-length Unicode string to 4,000 characters
TEXTVariable-length non-Unicode string to 2,147,483,647 characters
NTEXTVariable-length Unicode string to 1,073,741,823 characters
BINARYFixed-length binary data up to 8,000 characters
VARBINARYVariable-length binary data up to 8,000 characters
IMAGEVariable-length binary data up to 2,147,483,647 characters

FUNÇÕES DE DATA
GETDATE()
SELECT CONVERT(CHAR(19),GETDATE(),120) --> 2015-02-24 07:56:03 

SELECT CONVERT(CHAR(6),GETDATE(),112) --> 201502

SELECT CONVERT(VARCHAR(19),GETDATE(),103) + ' ' + 
       CONVERT(VARCHAR(8),GETDATE(),114) --> 17/04/2015 19:02:52
 
GETDATE() - partes da data
SELECT DATEPART(yyyy,getdate()) ano, 
DATEPART(mm,getdate()) mes, 
DATEPART(dd,getdate()) dia 

GETDATE() - adiciona ano,mes...
SELECT CONVERT(CHAR(10),getdate(),120), CONVERT(CHAR(10),DATEADD(dd,15,getdate()),120)

GETDATE() - diferencas entre datasSELECT DATEDIFF(dd,'2014-06-15','2014-08-25') dias

obs:  
yyyy(ano),
mm(mes),
dd(dia),
hh(hora),
mi(minutos),
ss(segundos)

BACKUP e RESTORE
BACKUP
EXEC SP_MSFOREACHTABLE @command1= "DBCC DBREINDEX ('?', '', 80)"

 backup log nomebase with truncate_only

 DBCC SHRINKDATABASE ('nomebase', 10);

 DECLARE     @name       VARCHAR(50),    -- database name  
            @path       VARCHAR(256),   -- path for backup files  
            @fileName   VARCHAR(256),   -- filename for backup  
            @fileDate   VARCHAR(20)     -- used for file name 

 SET @path           = 'D:\TPPRODUCAO\SQL_DADO\CARTAO1\bk\'  
SET @name           = 'nomebase'  
SET @fileDate       = CONVERT(VARCHAR(20),GETDATE(),112)+
                        '_'+
                        SUBSTRING(CONVERT(VARCHAR(20),GETDATE(),108),1,2)+  
                        SUBSTRING(CONVERT(VARCHAR(20),GETDATE(),108),4,2)+  
                        SUBSTRING(CONVERT(VARCHAR(20),GETDATE(),108),7,2)  
DECLARE db_cursor CURSOR FOR  
    SELECT 
            name 
    FROM 
            master.dbo.sysdatabases 
    WHERE 
            name = @name

 OPEN db_cursor    
FETCH NEXT FROM db_cursor INTO @name   
WHILE @@FETCH_STATUS = 0   
BEGIN   
    SET 
        @fileName   = @path + 
                      @name + 
                      '_' + 
                      @fileDate + '.BAK'  
    BACKUP DATABASE @name TO DISK = @fileName  

     FETCH NEXT FROM db_cursor INTO @name   
END   

 CLOSE db_cursor   
DEALLOCATE db_cursor  
>>> rode no query analyser

RESTORE
RESTORE DATABASE nomebase FROM DISK = 'D:\arq.BAK' WITH REPLACE, MOVE 'arq_Data' TO 'D:\arq_Data.MDF', MOVE 'arq_Log' TO 'D:\arq_Log.LDF'

ATTACH DATABASE 
Exec sp_attach_db 'nomebase','D:\arq_Data.MDF','D:\arq_Log.LDF'

CRIA USUARIO 
EXEC sp_addlogin 'usrbase','usr_senha','nomebase'
GO
-- coloca atributo de administrador para rodar  master..xp_cmdshell
EXEC master..sp_addsrvrolemember @loginame = 'usrcartao1', @rolename = N'sysadmin'

USE nomebase
GO
EXEC sp_changedbowner 'usrbase'
GO

RE-INDEXAÇÃO DATABASE + SHRINK
EXEC SP_MSFOREACHTABLE @command1= "DBCC DBREINDEX ('?', '', 80)"
backup log nomebase with truncate_only
DBCC SHRINKDATABASE ('nomebase', 10);

Gera um arquivo texto a partir de um select de dentro de uma procedure
-- C:\Temp\LANCREDITO.TXT deve ser um diretorio na maquina onde a base esta rodando
EXEC master..xp_cmdshell 'bcp "SELECT NSUCREDITO,DTEMISSAO,VALOR FROM LANCREDITO" queryout "C:\Temp\LANCREDITO.TXT" -c -t, -S  -Uusuario -Psenha -Sdatabase',NO_OUTPUT

Criar arquivo texto a partir de procedure
-- cria o arquivo.txt e escreve 'TESTE_01' no arquivo
DECLARE @EXEC_SQL VARCHAR(512)
SET
    @EXEC_SQL = 'echo TESTE_01 > c:\xx.txt'

EXEC master..xp_cmdshell @EXEC_SQL,NO_OUTPUT

-- abre arquivo.txt e escreve 'TESTE_02' no arquivo (continua escrevendo no final do arquivo)
SET
    @EXEC_SQL = 'echo TESTE_02 >> c:\xx.txt'

EXEC master..xp_cmdshell @EXEC_SQL,NO_OUTPUT

-- excluir arquivo.txt
EXEC MASTER..XP_CMDSHELL 'del D:\arquivo.txt'
-- exportar campos de uma tabela para arquivo.txt
EXEC master.DBO.xp_cmdshell 'bcp "SELECT CNPJ_ESTABELECIMENTO FROM alocred_operadora" QUERYOUT C:\arquivo.txt -S NomeBancoDados -U usuario -P senha -t ; -r \n -w',,NO_OUTPUT


Tira backup da transação a partir de procedure
-- cria o arquivo.txt e escreve 'TESTE_01' no arquivo 
-- BCP "SELECT NSUCREDITO FROM LANCREDITO WHERE NSUCREDITO='20150301110717505'" queryout C:\CREDITOAUTORIZADOR_20150301110717505.SQL -c -E -Uusuario -Psenha -Sbasedados
IF (@RETORNOCREDITOAUTORIZADOR='00')
BEGIN
    SET NOCOUNT ON
    SET
        @EXEC_SQL = 'ECHO '+
                    RTRIM(LTRIM(ISNULL(@NSUPARCELACREDITO,'')))+
                    ' > '+
                    @PAR_DIR_BK_AUTORIZADOR+
                    '\'+                 'CREDITOAUTORIZADOR_'+RTRIM(LTRIM(@NSUPARCELACREDITO))+'.SQ0'

-- evita o retorno do MASTER..XP_CMDSHELL na procedure
    CREATE TABLE #BKTEMP (LINHA VARCHAR(500) NULL)
    INSERT #BKTEMP
    EXEC MASTER..XP_CMDSHELL @EXEC_SQL,NO_OUTPUT    
    DROP TABLE #BKTEMP
END 

Copiar arquivos a partir de uma procedure
-- @rc = 0 copiou com sucesso,<> 0 erro 
DECLARE @rc int
EXEC @rc = master.dbo.xp_cmdshell 'copy c:\temp\RelGerencial_00000316.pdf c:\temp\RelGerencial_00000316.txt'
PRINT @rc

Dir (lista diretorio) para um arquivo a partir de uma procedure
exec master.dbo.xp_cmdshell 'dir > c:\temp\*.sql',NO_OUTPUT
 
Cria um diretorio a partir de uma procedure
exec master.dbo.xp_cmdshell 'mkdir "c:\temp\SQL Agent Output\new_job\"',NO_OUTPUT