Olá, mundo!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Publicado em Não categorizado | 1 Comentário

Como listar relacionamentos de uma tabela

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
AND PK.TABLE_NAME=’OBJETO’
—- optional:
ORDER BY
1,2,3,4

Publicado em SQL Server | Deixe um comentário

Como inserir dados de uma tabela para outra SQL Server

Para os casos em que já existe a tabela de destino o comando é o seguinte:
--Insere registros de uma outra tabela

INSERT INTO TabelaDestino (Nome, Sobrenome)
SELECT Primeiro_nome Nome,  Sobrenome  --A nomenclatura dos campos no select deve seguir a nomenclatura da tabela de destino
FROM Usuarios



Publicado em SQL Server | Deixe um comentário

Cursor no SQL Server

CREATE TABLE #teste (id INT, nome VARCHAR(50), data DATETIME) 
INSERT #teste (id, nome, data) VALUES(1,’Joao’,’2008-01-01′) 
INSERT #teste (id, nome, data) VALUES(2,’Maria’,’2008-06-20′) 
INSERT #teste (id, nome, data) VALUES(3,’José’,’2008-03-12′) 
DECLARE  @id INT 
DECLARE  @nome VARCHAR(50) 
DECLARE  @data DATETIME 
DECLARE db_cursor CURSOR FOR SELECT * FROM #teste 
OPEN db_cursor 
FETCH NEXT FROM db_cursor 
INTO @id, @nome, @data 
WHILE @@FETCH_STATUS = 0 
  BEGIN 
    PRINT (‘CODIGO:’ + Cast(@ID AS VARCHAR(5)) + ‘ NOME:’ + @NOME + ‘ DATA:’ + Cast(@DATA AS VARCHAR(25))) 
     
    FETCH NEXT FROM db_cursor 
    INTO @id, @nome, @data 
  END 
CLOSE db_cursor 
DEALLOCATE db_cursor 
DROP TABLE #teste

Publicado em SQL Server | Deixe um comentário

Listar tabelas e colunas no SQL Server

Comando para listar tabelas no SQL Server:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS


Comando para listar as colunas ou campos de uma tabela:

SELECT NAME FROM SYSCOLUMNS WHERE ID = OBJECT_ID(‘<nome da tabela>’)


Comando para listar todas as tabelas e colunas:

SELECT t.name AS tablename, c.name AS columnname

FROM sys.tables AS t

INNER JOIN sys.columns AS c ON t.object_id = c.object_id

ORDER BY tablename, columnname


Comando para listar todas as tabelas e colunas:

 SELECT distinct  

 [Id Tabela] = c.object_id,  

 [Tabela] = OBJECT_NAME(c.object_id),   

 [Coluna] = c.name,  

 [Tipo] = tp.name,  

 [Tamanho] = c.max_length,  

 [Descricao] = ex.value  

 FROM sys.columns c  

 INNER JOIN sys.systypes tp  

   on tp.xtype = c.system_type_id  

 LEFT OUTER JOIN  

 sys.extended_properties ex  

 ON ex.major_id = c.object_id  

 AND ex.minor_id = c.column_id  

 AND ex.name = ‘MS_Description’  

 WHERE OBJECTPROPERTY(c.object_id, ‘IsMsShipped’)=0  

 AND c.OBJECT_ID = OBJECT_ID(‘AFPAR_PARAMETRO’)  

 and ex.class_desc = ‘OBJECT_OR_COLUMN’  

 ORDER BY OBJECT_NAME(c.object_id)

Publicado em SQL | Deixe um comentário

Obter informações das tabelas, colunas e chaves do SQL Server

Comandos para retornar informações sobre tabelas, colunas e chaves do SQL Server

 

Chaves estrangeiras

 

Opção 1:

sp_fkeys (T-SQL)

Returns logical foreign key information for the current environment. This procedure shows foreign key relationships including disabled foreign keys.

Syntax

sp_fkeys     [@pktable_name =] pktable_name
    [,[@pktable_owner =] pktable_owner]
    [,[@pktable_qualifier =] pktable_qualifier]
    {, [@fktable_name =] fktable_name}
    [,[@fktable_owner =] fktable_owner]
    [,[@fktable_qualifier =] fktable_qualifier]

Arguments
[@pktable_name =] pktable_name

Is the name of the table (with the primary key) used to return catalog information. pktable_name is sysname, with a default of NULL. Wildcard pattern matching is not supported. This parameter or the fktable_name parameter, or both, must be supplied.

[@pktable_owner =] pktable_owner

Is the name of the owner of the table (with the primary key) used to return catalog information. pktable_owner is sysname, with a default of NULL. Wildcard pattern matching is not supported. If pktable_owner is not specified, the default table visibility rules of the underlying DBMS apply.

In Microsoft® SQL Server™, if the current user owns a table with the specified name, that table’s columns are returned. If pktable_owner is not specified and the current user does not own a table with the specified pktable_name, the procedure looks for a table with the specified pktable_name owned by the database owner. If one exists, that table’s columns are returned.

[@pktable_qualifier =] pktable_qualifier

Is the name of the table (with the primary key) qualifier. pktable_qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, the qualifier represents the database name. In some products, it represents the server name of the table’s database environment.

[@fktable_name =] fktable_name

Is the name of the table (with a foreign key) used to return catalog information. fktable_name is sysname, with a default of NULL. Wildcard pattern matching is not supported. This parameter or the pktable_name parameter, or both, must be supplied.

[@fktable_owner =] fktable_owner

Is the name of the owner of the table (with a foreign key) used to return catalog information. fktable_owner is sysname, with a default of NULL. Wildcard pattern matching is not supported. If fktable_owner is not specified, the default table visibility rules of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified name, that table’s columns are returned. If fktable_owner is not specified and the current user does not own a table with the specified fktable_name, the procedure looks for a table with the specified fktable_name owned by the database owner. If one exists, that table’s columns are returned.

[@fktable_qualifier =] fktable_qualifier

Is the name of the table (with a foreign key) qualifier. fktable_qualifier is sysname, with a default of NULL. In SQL Server, the qualifier represents the database name. In some products, it represents the server name of the table’s database environment.
Return Code Values

None

Result Sets
Column name

Data type

Description

PKTABLE_QUALIFIER

sysname

Name of the table (with the primary key) qualifier. This field can be NULL.

PKTABLE_OWNER

sysname

Name of the table (with the primary key) owner. This field always returns a value.

PKTABLE_NAME

sysname

Name of the table (with the primary key). This field always returns a value.

PKCOLUMN_NAME

sysname

Name of the primary key column(s), for each column of the TABLE_NAME returned. This field always returns a value.

FKTABLE_QUALIFIER

sysname

Name of the table (with a foreign key) qualifier. This field can be NULL.

FKTABLE_OWNER

sysname

Name of the table (with a foreign key) owner. This field always returns a value.

FKTABLE_NAME

sysname

Name of the table (with a foreign key). This field always returns a value.

FKCOLUMN_NAME

varchar(32)

Name of the foreign key column(s), for each column of the TABLE_NAME returned. This field always returns a value.

KEY_SEQ

smallint

Sequence number of the column in a multicolumn primary key. This field always returns a value.

UPDATE_RULE

smallint

Action applied to the foreign key when the SQL operation is an update. SQL Server returns 1 for these columns. Open Data Services gateways can return values of 0, 1, or 2:
0 = CASCADE changes to foreign key.
1 = RESTRICT changes if foreign key
        is present.
2 = SET_NULL; set foreign key to
        NULL.

DELETE_RULE

smallint

Action applied to the foreign key when the SQL operation is a deletion. SQL Server returns 1 for these columns. Open Data Services gateways can return values of 0, 1, or 2:
0 = CASCADE changes to foreign key.
1 = RESTRICT changes if foreign key
        is present.
2 = SET_NULL; set foreign key to
        NULL.

FK_NAME

sysname

Foreign key identifier. It is NULL if not applicable to the data source. SQL Server returns the FOREIGN KEY constraint name.

PK_NAME

sysname

Primary key identifier. It is NULL if not applicable to the data source. SQL Server returns the PRIMARY KEY constraint name.

The results returned are ordered by FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, and KEY_SEQ.

Remarks

Application coding that includes tables with disabled foreign keys can be implemented by:

  • Temporarily disabling constraint checking (ALTER TABLE NOCHECK or CREATE TABLE NOT FOR REPLICATION) while working with the tables, and enabling it again later.
  • Using triggers or application code to enforce relationships.

If the primary key table name is supplied and the foreign key table name is NULL, sp_fkeys returns all tables that include a foreign key to the given table. If the foreign key table name is supplied and the primary key table name is NULL, sp_fkeys returns all tables related by a primary key/foreign key relationship to foreign keys in the foreign key table. Note that this procedure does not support the common key type as specified in the SQL Server syskeys system table.

The sp_fkeys stored procedure is equivalent to SQLForeignKeys in ODBC.

Permissions

Execute permissions default to the public role.

Examples

This example retrieves a list of foreign keys for the Customers table in the Northwind database.

USE Northwind

EXEC sp_fkeys @pktable_name = N'Customers'

  

See Also

sp_pkeys

 

Opção 2:

SELECT

K_Table = FK.TABLE_NAME,

FK_Column

= CU.COLUMN_NAME,

PK_Table

= PK.TABLE_NAME,

PK_Column = PT.COLUMN_NAME,

Constraint_Name = C.CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

INNER

JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

INNER

JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

INNER

JOIN (

SELECT i1.TABLE_NAME, i2.COLUMN_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME

WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’

) PT ON PT.TABLE_NAME = PK.TABLE_NAME

—- optional:

ORDER

BY

1

,2,3,4

WHERE

PK.TABLE_NAME=‘table’

Publicado em SQL Server | Deixe um comentário

MemberWiseClone

Memberwiseclone
Uma maneira de fazer uma cópia de uma instância de um objeto:
 
using System;

public

class Nome

{

public string nome;

public string sobrenome;

public Nome(string nome, string sobrenome)

{

this.nome = nome;

this.sobrenome = sobrenome;

}

public void Display()

{

Console.WriteLine("Nome = " + nome);

Console.WriteLine("Sobrenome = " + sobrenome);

}

public static Nome Copy(Nome nome)

{

return (Nome)nome.MemberwiseClone();

}
}
 

class

Test

{
public static void Main()
{
Nome meuNome = new Nome("Gustavo", "Porto");
Nome meuNomeOld = Nome.Copy(meuNome);
meuNome.nome="João";
meuNome.sobrenome = "Silva";
Console.WriteLine("Meu nome:");
meuNome.Display();
Console.WriteLine("Executa o memberwise Clone:");
Console.WriteLine("Meu nome old:");
meuNomeOld.Display();
Console.ReadKey();
}
}
Publicado em C# | Deixe um comentário