Gerar todas as chaves estrangeiras (primary keys) para o banco de dados

– Get all existing primary keys
DECLARE cPK CURSOR FOR

SELECT DISTINCT C.TABLE_NAME, C.CONSTRAINT_NAME , F.NAME
FROM SYS.INDEXES I
INNER JOIN SYS.FILEGROUPS F
ON I.DATA_SPACE_ID = F.DATA_SPACE_ID
INNER JOIN SYS.ALL_OBJECTS O
ON I.[OBJECT_ID] = O.[OBJECT_ID]
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON O.NAME = C.TABLE_NAME
WHERE C.CONSTRAINT_TYPE = ‘PRIMARY KEY’
ORDER BY C.TABLE_NAME

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
DECLARE @FileName SYSNAME

–Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName , @FileName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ”
SET @PKSQL = ‘ALTER TABLE ‘ + @PkTable + ‘ ADD CONSTRAINT ‘ + @PkName + ‘ PRIMARY KEY CLUSTERED (‘

–Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName
ORDER BY ORDINAL_POSITION
OPEN cPKColumn

DECLARE @PkColumn SYSNAME
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
– Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
SET @PKSQL = @PKSQL + ‘, ‘

SET @PKSQL = @PKSQL + @PkColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ‘)’ + ‘ ON ‘+@FileName
– Print the primary key statement
PRINT @PKSQL

FETCH NEXT FROM cPK INTO @PkTable, @PkName, @FileName
END
CLOSE cPK
DEALLOCATE cPK

sp_MSforeachdb e sp_MSforeachtable

Sempre que precisamos executar alguma instrução em todos os databases ou em todas as tabelas de um database, logo pensamos em usar cursor. No entanto, existe duas stored procedures não documentadas que podem resolver este problema de forma bastante simples. Este artigo discute como usar as procedures sp_MSforeachdb e sp_MSforeachtable.

Para ler o artigo completo e saber como utilizar estas procedures, visite o link http://www.dbazine.com/sql/sql-articles/larsen5″

Procurando uma palavra nas stored procedures do database

Em determinadas situações precisamos procurar uma palavra nas stored procedures de um determinado database. Abaixo segue um script em Transact-SQL que retorna o nome da(s) stored procedure(s). Atente para o fato de trocar DtgSchemaOBJECT pela palavra desejada:

declare @searchString varchar(100)

Set @searchString = ‘%’ + ‘DtgSchemaOBJECT’ + ‘%’

SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = ‘P’
AND SC.Text LIKE @searchString
ORDER BY SO.Name

Texto escrito por Alexandre Lopes para meus estudos de SQL SERVER

ALTERAR SCHEMA SQLSERVER

– alterar SCHEMA todas SPs
SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Procedures p
INNER JOIN sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘dbo’

– alterar SCHEMA todas TABELAS
exec sp_MSforeachtable “ALTER SCHEMA dbo TRANSFER ? PRINT ‘? modified’ “

– alterar SCHEMA todas VIEWS
SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Views p
INNER JOIN sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘dbo’

Problemas com criação de um Diagrama (database diagrams) no sql2005

 

erro = “Database diagram support objects cannot be installed because this database does not have a valid owner”

No SQL Server Management Studio verifique:

 

1. Right Click no seu database, vá em “properties”

2. Acesse a opção “Options” em “Select a page”

3. Veja se a combo “Compatibility Level” está em “SQL Server 2005(90)”

 

Se tudo estiver correto mude sua autorização para “sa” com o comando abaixo:

 
ALTER AUTHORIZATION ON DATABASE::[Nome do Database] TO sa

Remover “caracteres especiais” em campos do SQL Server

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ”), CHAR(13), ”), CHAR(9), ”)

Fazendo um SELECT RANDÔMICO

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Thanks Tim

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Resultado de um SELECT separado por VIRGULA

DECLARE @resultado VARCHAR(MAX)
SELECT @resultado = COALESCE(@resultado + ‘,’ ,”) + [coluna da tabela]
FROM [tabela]
SELECT @resultado

Função Obter o Dia da Semana

CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
            AS
    BEGIN
    DECLARE @rtDayofWeek VARCHAR(10)
    DECLARE @weekDay INT
        – Here I have subtracted 7 For keeping Sunday as the First day
        – like wise for Monday we need to subtract 2 and so on
        SET @weekDay = ((DATEPART(dw,GETDATE())+@@DATEFIRST-7)%7)
    SELECT @rtDayofWeek = CASE @weekDay
                    WHEN 1 THEN ‘Sunday’
                    WHEN 2 THEN ‘Monday’
                    WHEN 3 THEN ‘Tuesday’
                    WHEN 4 THEN ‘Wednesday’
                    WHEN 5 THEN ‘Thursday’
                    WHEN 6 THEN ‘Friday’
                    WHEN 7 THEN ‘Saturday’
        END
    RETURN (@rtDayofWeek)
    END
GO
SELECT dbo.udf_DayOfWeek(GETDATE())

(script modificado por Mihir Pop)

 

Quais Store Procedures foram modificadas

– 7 no final do código = 7 dias atrás
SELECT name
    FROM sys.objects
    WHERE type = ‘P’
        AND DATEDIFF(D,modify_date, GETDATE()) < 7

Em tempo – Você pode alterar o type P para qualquer um listado abaixo:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure