Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

Archive for julho \24\UTC 2009

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

Posted by karuta em julho 24, 2009

— 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

Anúncios

Posted in store procedure | Leave a Comment »

Linhas FINAS nas tabelas

Posted by karuta em julho 23, 2009

Basta adicionar na sua tabela o comando CSS: style=”border-collapse:collapse;”

exemplo:
table border=”1″ style=”border-collapse:collapse;” cellpadding=”2″

Posted in html | Leave a Comment »