Obtendo campos
Colegas,
Seguinte, tenho um banco de dados Fb o qual consigo obter os campos das tabelas com o seguinte código:
' SELECT A.RDB$FIELD_NAME AS NM_FIELD,'+
' C.RDB$FIELD_NAME AS NM_FIELD_KEY,'+
' COALESCE(A.RDB$NULL_FLAG, D.RDB$NULL_FLAG) AS LG_NOTNULL'+
' FROM RDB$RELATION_FIELDS A'+
' LEFT OUTER JOIN RDB$RELATION_CONSTRAINTS B ON'+
' (B.RDB$RELATION_NAME = A.RDB$RELATION_NAME AND'+
' B.RDB$CONSTRAINT_TYPE = ''PRIMARY KEY'')'+
' LEFT OUTER JOIN RDB$INDEX_SEGMENTS C ON '+
' (C.RDB$INDEX_NAME = B.RDB$INDEX_NAME AND '+
' C.RDB$FIELD_NAME = A.RDB$FIELD_NAME)'+
' LEFT OUTER JOIN RDB$FIELDS D ON'+
' (D.RDB$FIELD_NAME = A.RDB$FIELD_SOURCE)'+
' WHERE UPPER(A.RDB$RELATION_NAME) = UPPER(%s)'; E tenho um banco Sql Server exatamente igual, mas não consigo fazer uma instrução para obter os campos como na base Fb, alguém poderia me ajudar? Aguardo
' C.RDB$FIELD_NAME AS NM_FIELD_KEY,'+
' COALESCE(A.RDB$NULL_FLAG, D.RDB$NULL_FLAG) AS LG_NOTNULL'+
' FROM RDB$RELATION_FIELDS A'+
' LEFT OUTER JOIN RDB$RELATION_CONSTRAINTS B ON'+
' (B.RDB$RELATION_NAME = A.RDB$RELATION_NAME AND'+
' B.RDB$CONSTRAINT_TYPE = ''PRIMARY KEY'')'+
' LEFT OUTER JOIN RDB$INDEX_SEGMENTS C ON '+
' (C.RDB$INDEX_NAME = B.RDB$INDEX_NAME AND '+
' C.RDB$FIELD_NAME = A.RDB$FIELD_NAME)'+
' LEFT OUTER JOIN RDB$FIELDS D ON'+
' (D.RDB$FIELD_NAME = A.RDB$FIELD_SOURCE)'+
' WHERE UPPER(A.RDB$RELATION_NAME) = UPPER(%s)'; E tenho um banco Sql Server exatamente igual, mas não consigo fazer uma instrução para obter os campos como na base Fb, alguém poderia me ajudar? Aguardo
Irenildo Carvalho
Curtidas 0
Respostas
Rodrigo Mourão
22/03/2010
Olá Amigo,
Poderia me informar qual versao do seu sqlserver (2000, 2005, 2008) pois no sql server vamos ter que dar select em tabelas de sistemas porem a estrutura e diferente.
att,
Poderia me informar qual versao do seu sqlserver (2000, 2005, 2008) pois no sql server vamos ter que dar select em tabelas de sistemas porem a estrutura e diferente.
att,
GOSTEI 0
Irenildo Carvalho
22/03/2010
Foi mal... Tô utilizando o Sql server 2005...
Valeu
GOSTEI 0
Irenildo Carvalho
22/03/2010
Foi mal... Tô utilizando o Sql server 2005...
Valeu
GOSTEI 0
Irenildo Carvalho
22/03/2010
Foi mal... Tô utilizando o Sql server 2005...
Valeu
GOSTEI 0
Irenildo Carvalho
22/03/2010
Foi mal... Tô utilizando o Sql server 2005...
Valeu
GOSTEI 0
Rodrigo Mourão
22/03/2010
Ok Irei verificar e entro em contato.
Att,
Att,
GOSTEI 0
Irenildo Carvalho
22/03/2010
Certo, vou aguardar...
Obrigado
Irenildo
GOSTEI 0
Rodrigo Mourão
22/03/2010
No SqlServer as informacoes referente ao seu banco de dados estao em um schema chamado INFORMAION_SCHEMA.
A sintaxe para acesso é: select * from SEUBANCO.INFORMATION_SCHEMA.TABELA
Por exemplo se seu banco é DBVendas e que pegar a informacao das colunas da tabela cliente poderá fazer:
Select * from DBVendas.INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Cliente';
Abaixo estão as principais tabelas de information_schema:
INFORMATION_SCHEMA.CHECK_CONSTRAINTS
INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
INFORMATION_SCHEMA.COLUMN_PRIVILEGES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.PARAMETERS
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
INFORMATION_SCHEMA.ROUTINE_COLUMNS
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.SCHEMATA
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.TABLE_PRIVILEGES
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
INFORMATION_SCHEMA.VIEW_TABLE_USAGE
INFORMATION_SCHEMA.VIEWS
Abaixo um exemplo para pegar as informçoes da base AdventureWorks:
Select * from AdventureWorks.INFORMATION_SCHEMA.CHECK_CONSTRAINTS
Select * from AdventureWorks.INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.COLUMN_PRIVILEGES
Select * from AdventureWorks.INFORMATION_SCHEMA.COLUMNS
Select * from AdventureWorks.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
Select * from AdventureWorks.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.PARAMETERS
Select * from AdventureWorks.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
Select * from AdventureWorks.INFORMATION_SCHEMA.ROUTINE_COLUMNS
Select * from AdventureWorks.INFORMATION_SCHEMA.ROUTINES
Select * from AdventureWorks.INFORMATION_SCHEMA.SCHEMATA
Select * from AdventureWorks.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Select * from AdventureWorks.INFORMATION_SCHEMA.TABLE_PRIVILEGES
Select * from AdventureWorks.INFORMATION_SCHEMA.TABLES
Select * from AdventureWorks.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.VIEW_TABLE_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.VIEWS
Att,
A sintaxe para acesso é: select * from SEUBANCO.INFORMATION_SCHEMA.TABELA
Por exemplo se seu banco é DBVendas e que pegar a informacao das colunas da tabela cliente poderá fazer:
Select * from DBVendas.INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Cliente';
Abaixo estão as principais tabelas de information_schema:
INFORMATION_SCHEMA.CHECK_CONSTRAINTS
INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
INFORMATION_SCHEMA.COLUMN_PRIVILEGES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.PARAMETERS
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
INFORMATION_SCHEMA.ROUTINE_COLUMNS
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.SCHEMATA
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.TABLE_PRIVILEGES
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
INFORMATION_SCHEMA.VIEW_TABLE_USAGE
INFORMATION_SCHEMA.VIEWS
Abaixo um exemplo para pegar as informçoes da base AdventureWorks:
Select * from AdventureWorks.INFORMATION_SCHEMA.CHECK_CONSTRAINTS
Select * from AdventureWorks.INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.COLUMN_PRIVILEGES
Select * from AdventureWorks.INFORMATION_SCHEMA.COLUMNS
Select * from AdventureWorks.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
Select * from AdventureWorks.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.PARAMETERS
Select * from AdventureWorks.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
Select * from AdventureWorks.INFORMATION_SCHEMA.ROUTINE_COLUMNS
Select * from AdventureWorks.INFORMATION_SCHEMA.ROUTINES
Select * from AdventureWorks.INFORMATION_SCHEMA.SCHEMATA
Select * from AdventureWorks.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Select * from AdventureWorks.INFORMATION_SCHEMA.TABLE_PRIVILEGES
Select * from AdventureWorks.INFORMATION_SCHEMA.TABLES
Select * from AdventureWorks.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.VIEW_TABLE_USAGE
Select * from AdventureWorks.INFORMATION_SCHEMA.VIEWS
Att,
GOSTEI 0
Rodrigo Mourão
22/03/2010
Olá Amigo, podemos ajudar em algo mais ??
Att,
Att,
GOSTEI 0
Irenildo Carvalho
22/03/2010
Por enquanto ta blz, muito obrigado Rodrigo
Um abraço
Irenildo
GOSTEI 0