1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| Dim sqlRequete As String
'Requete de lecture des champs de la table avec leurs attributs
sqlRequete = "SELECT INFORMATION_SCHEMA.COLUMNS.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME," _
& " INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT" _
& ", INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE, " _
& " INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH, RQ1.id, COALESCE (RQ3.cle, 0) AS cle" _
& ", INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION," _
& " INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE, RQ2.is_identity FROM" _
& " INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN (SELECT COLUMN_NAME, TABLE_NAME, '1' AS" _
& " is_identity FROM INFORMATION_SCHEMA.COLUMNS AS COLUMNS_1 WHERE (TABLE_SCHEMA = 'dbo')" _
& " AND (COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1)) AS RQ2 ON " _
& " INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = RQ2.COLUMN_NAME AND " _
& " INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = RQ2.TABLE_NAME LEFT OUTER JOIN (SELECT TOP (1)" _
& " id, name FROM sys.sysobjects WHERE (xtype = 'U')) AS RQ1 ON RQ1.name =" _
& " INFORMATION_SCHEMA.COLUMNS.TABLE_NAME LEFT OUTER JOIN (SELECT '1' AS cle, COLUMN_NAME," _
& " TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE" _
& " (OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsPrimaryKey') = 1)) AS RQ3 ON " _
& " RQ3.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND RQ3.COLUMN_NAME =" _
& " INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME WHERE (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME ='" _
& Replace(NomTable, "'", "''") & "') ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION"
Dim dttable As New DataTable
dttable = sql_reader.EnvoieRequete(sqlRequete) |
Partager