Utilisation de sqldeveloper pour accéder à une base Microsoft_SQL_Server
Posté par patrick le août 20, 2007
Toujours dans le but de développer des applications C# à partir du mois de septembre, je regarde les outils graphiques que je peux utiliser pour voir le contenu d’une base de données Microsoft SQL Server. Sous Oracle, j’avais utilisé sqldeveloper et j’en étais très content. La version 1.2 de sqldeveloper permet d’accéder aux bases de données MySql, Access et SqlServer. Pour Sqlserver, il faut juste charger un pilote que l’on peut trouver là (il faut d’abord s’enregistrer auprès d’oracle): http://sourceforge.net/project/showfiles.php?group_id=33291(“Open source JDBC 3.0 Type 4 driver for Microsoft SQL Server (6.5, 7.0, 2000 and 2005) and Sybase. jTDS is the fastest JDBC driver for MS SQL Server and is a complete implementation of the JDBC spec. For more information see http://jtds.sourceforge.net/“).
Pour installer le pilote jTDS voir http://forums.oracle.com/forums/thread.jspa?messageID=1762500 (“Download jTDS – SQL Server and Sybase JDBC driver from http://sourceforge.net/project/showfiles.php?group_id=33291(“In Oracle SQL Developer open Tools -> Preferences, Database -> Third Party JDBC Drivers, Add Entry, Browse the unzipped driver and add the jtds-1.2.jar file“).
Et ça marche !
Utilisation des méta-données de sqlserver (information_schema) pour connaitre le contenu d’une base de données:
- ’select * from vs2005db.information_schema.tables’ (‘vs2005db’ est le nom de votre base de données appelée aussi ‘TABLE_CATALOG’
VS2005DB dbo ALBUMS BASE TABLE
VS2005DB dbo DESSINATEUR BASE TABLE
VS2005DB dbo GroupeDetail BASE TABLE
….
VS2005DB dbo Ville BASE TABLE
- select column_name, data_type, character_maximum_length from vs2005db.information_schema.columns where table_name=’albums’;
ID bigint (null)
TITRE varchar 100
NOMBRE_PLANCHES int (null)
DATE_SORTIE datetime (null)
INDICATEUR_COULEUR bit (null)
ID_SERIE bigint (null)
RESUME varchar 500
Pour plus d’infos voir http://www.sqlteam.com/article/using-metadata
View Name Description CHECK_CONSTRAINTS Holds information about constraints in the database COLUMN_DOMAIN_USAGE Identifies which columns in which tables are user-defined datatypes COLUMN_PRIVILEGES Has one row for each column level permission granted to or by the current user COLUMNS Lists one row for each column in each table or view in the database CONSTRAINT_COLUMN_USAGE Lists one row for each column that has a constraint defined on it CONSTRAINT_TABLE_USAGE Lists one row for each table that has a constraint defined on it DOMAIN_CONSTRAINTS Lists the user-defined datatypes that have rules bound to them DOMAINS Lists the user-defined datatypes KEY_COLUMN_USAGE Lists one row for each column that’s defined as a key PARAMETERS Lists one row for each parameter in a stored procedure or user-defined function REFERENTIAL_CONSTRAINTS Lists one row for each foreign constraint ROUTINES Lists one row for each stored procedure or user-defined function ROUTINE_COLUMNS Contains one row for each column returned by any table-valued functions SCHEMATA Contains one row for each database TABLE_CONSTRAINTS Lists one row for each constraint defined in the current database TABLE_PRIVILEGES Has one row for each table level permission granted to or by the current user TABLES Lists one row for each table or view in the current database VIEW_COLUMN_USAGE Lists one row for each column in a view including the base table of the column where possible VIEW_TABLE_USAGE Lists one row for each table used in a view VIEWS Lists one row for each view
Exemples:
- select constraint_name,column_name from vs2005db.information_schema.KEY_COLUMN_USAGE where table_name=’albums’;
FK_ALBUMS_SERIE ID_SERIE
PK_ALBUMS ID
-select constraint_name, constraint_type from vs2005db.information_schema.table_constraints where table_name=’albums’;
PK_ALBUMS PRIMARY KEY
FK_ALBUMS_SERIE FOREIGN KEY
