Liste des tables avec des clés de type varchar

Bonjour Laurent, Get this :

Select DISTINCT O.name as "Table", C.name as Champ, T.name as Type
From sysobjects O, syscolumns C, systypes T, sysforeignkeys PFK
WHERE O.id = C.id AND
C.xtype = T.xtype AND
T.xtype IN (175, 239, 167) AND
((O.id = PFK.fkeyid AND C.colid = PFK.fkey) OR
(O.id= PFK.rkeyid AND C.colid=1))

J'ai testé sur northwind ça a l'air d'être exhaustif :)
Fred. M.

"Laurent" a écrit :

Bonjour à tous,

je cherche comment faire un script qui me donnerai sur une base sql 2000, la
liste
de toutes les tables possédant des clés primaires ou étrangère de type
varchar ou char avec les noms de champs

Merci d'avance

Laurent

Bonjour

Fred.M. a écrit :

Bonjour Laurent, Get this :

Select DISTINCT O.name as "Table", C.name as Champ, T.name as Type
From sysobjects O, syscolumns C, systypes T, sysforeignkeys PFK
WHERE O.id = C.id AND
C.xtype = T.xtype AND
T.xtype IN (175, 239, 167) AND
((O.id = PFK.fkeyid AND C.colid = PFK.fkey) OR
(O.id= PFK.rkeyid AND C.colid=1))

Quelle horeur !!! ;-)

Ne JAMAIS utiliser les tables systèmes. En effet MS se réserve le droit
de les modifier sans préavis.

Utilisez les vues d'information de schéma (norme SQL), les procédures
stockées système ou encore les vues de management (2005), mais évitez
TOUJOURS si possible l'accès direct aux tabkles système.

De plus faites vos jointures dans une clause FROM et pas dans le where !

Voici donc une version correcte :

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
AND TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON CCU.TABLE_CATALOG = C.TABLE_CATALOG
AND CCU.TABLE_SCHEMA = C.TABLE_SCHEMA
AND CCU.TABLE_NAME = C.TABLE_NAME
AND CCU.COLUMN_NAME = C.COLUMN_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND DATA_TYPE LIKE '%char%'
AND TC.TABLE_SCHEMA + '.' + TC.TABLE_NAME <> 'dbo.dtproperties'

A +

je cherche comment faire un script qui me donnerai sur une base sql 2000, la
liste de toutes les tables possédant des clés primaires ou étrangère de type
varchar ou char avec les noms de champs

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Bonjour,
Je me permets de rebondir sur ces propos plein de dogmatisme que j'ai bien
vu par ailleurs. En tant moi même qu'ancien MCT pendant 6 ans, je connais
bien ce genre de discours Proutproutiste qui conviennent à ce que "c'est pas
comme ça qon fait, c'est écrit autrement dans les bouquins".
- Les modifications de tables system sont identiables au sein des différents
fix et autres services packs.
- A part pour faire "puriste", les jointures en clause WHERE sont normés
ANSI 92 donc pas de contrindications réelles (execute une même requête en
jointure INNER JOIN puis en clause WHERE, et tu observeras que le plan
d'exécution et tes IOs seront identiques !). Et si je suis passé par une
clause WHERE, c'est tout simplement à cause de l'opérateur OR.
- J'ai la chance d'avoir 4 collaborateurs qui sont directement de chez Ms,
et je ne leur casse jamais les pieds en leur disant "rhôô ton script il est
pas beau, t'as pas le droit de référencer une table system".
- Les Vues d'information de schema ne sont pas aussi riches et exhaustives
que les données system (même si pour cet exemple elles en conviennent).
- Oui en revanche pour les vues managées pour la version 2005.
- Enfin si je lis ta requête, elle ne remonte que les cas concernant les Clé
primaires uniquement alors que Laurent pose la question sur les clés
étrangères également.

Cordialement tout ça tout ça...,

Fred.M.
(non je ne ferai pas ici l'étalage de mon CV)



"Fred BROUARD" a écrit :

Bonjour

Fred.M. a écrit :
> Bonjour Laurent, Get this :
>
> Select DISTINCT O.name as "Table", C.name as Champ, T.name as Type
> From sysobjects O, syscolumns C, systypes T, sysforeignkeys PFK
> WHERE O.id = C.id AND
> C.xtype = T.xtype AND
> T.xtype IN (175, 239, 167) AND
> ((O.id = PFK.fkeyid AND C.colid = PFK.fkey) OR
> (O.id= PFK.rkeyid AND C.colid=1))


Quelle horeur !!! ;-)

Ne JAMAIS utiliser les tables systèmes. En effet MS se réserve le droit
de les modifier sans préavis.

Utilisez les vues d'information de schéma (norme SQL), les procédures
stockées système ou encore les vues de management (2005), mais évitez
TOUJOURS si possible l'accès direct aux tabkles système.

De plus faites vos jointures dans une clause FROM et pas dans le where !

Voici donc une version correcte :

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
AND TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON CCU.TABLE_CATALOG = C.TABLE_CATALOG
AND CCU.TABLE_SCHEMA = C.TABLE_SCHEMA
AND CCU.TABLE_NAME = C.TABLE_NAME
AND CCU.COLUMN_NAME = C.COLUMN_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND DATA_TYPE LIKE '%char%'
AND TC.TABLE_SCHEMA + '.' + TC.TABLE_NAME <> 'dbo.dtproperties'

A +



>> je cherche comment faire un script qui me donnerai sur une base sql 2000, la
>> liste de toutes les tables possédant des clés primaires ou étrangère de type
>> varchar ou char avec les noms de champs


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Fred.M. a écrit :

Bonjour,
Je me permets de rebondir sur ces propos plein de dogmatisme que j'ai bien
vu par ailleurs. En tant moi même qu'ancien MCT pendant 6 ans, je connais
bien ce genre de discours Proutproutiste qui conviennent à ce que "c'est pas
comme ça qon fait, c'est écrit autrement dans les bouquins".

Pas seulement les bouquins, la norme SQL qui est ISO depuis plusieurs
décennies (1987)

- Les modifications de tables system sont identiables au sein des différents
fix et autres services packs.

Qui les lit ?

- A part pour faire "puriste", les jointures en clause WHERE sont normés
ANSI 92 donc pas de contrindications réelles (execute une même requête en
jointure INNER JOIN puis en clause WHERE, et tu observeras que le plan
d'exécution et tes IOs seront identiques !). Et si je suis passé par une
clause WHERE, c'est tout simplement à cause de l'opérateur OR.

Erreur !
La norme SQL2 de 1992 fait obsolescence de la jointure dans le WHERE et
introduit le mot clef JOIN...

- J'ai la chance d'avoir 4 collaborateurs qui sont directement de chez Ms,
et je ne leur casse jamais les pieds en leur disant "rhôô ton script il est
pas beau, t'as pas le droit de référencer une table system".

C'est votre problème... Cepandant, même l'aide en ligne MS indique qu'il
est préférable d'éviter d'utiliser les tables système.
Extrait de l'aide en ligne : (index : tables système, extraction des
informations)
"
[...]
Les applications doivent au contraire utiliser l'un des composants
suivants pour extraire les informations stockées dans les tables système :
- vues des schémas d'information ;
- Procédures stockées du système
- instructions et fonctions Transact-SQL ;
- SQL-DMO.
- fonctions du catalogue de l'interface de programmation d'applications
(API) de la base de données.

Ces composants constituent une API publiée permettant d'obtenir des
informations sur le système à partir de SQL Server. Microsoft gère la
compatibilité de ces composants d'une version à l'autre. Le format des
tables système dépend de l'architecture interne de SQL Server et peut
changer d'une version à l'autre. Par conséquent, il se peut que les
applications ayant directement accès aux colonnes non documentées des
tables système doivent être préalablement modifiées avant de pouvoir
accéder à une version ultérieure de SQL Server.
"

Je m'étonne donc que des professionnels de chez MS ne respectent pas les
règles qu'ils ont eux même fixés !!!

- Les Vues d'information de schema ne sont pas aussi riches et exhaustives
que les données system (même si pour cet exemple elles en conviennent).
- Oui en revanche pour les vues managées pour la version 2005.

C'est un mieux !

- Enfin si je lis ta requête, elle ne remonte que les cas concernant les Clé
primaires uniquement alors que Laurent pose la question sur les clés
étrangères également.

Puisque tu n'a pas l'air d'être familiarisé ni avec les vues
d'information de schéma ni les jointures normalisées, je te donne la
modification au demurant trsè simple qu'il suffisait d'introduire pour
réaliser le souhait de laurent :

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
AND TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON CCU.TABLE_CATALOG = C.TABLE_CATALOG
AND CCU.TABLE_SCHEMA = C.TABLE_SCHEMA
AND CCU.TABLE_NAME = C.TABLE_NAME
AND CCU.COLUMN_NAME = C.COLUMN_NAME
WHERE CONSTRAINT_TYPE IN('PRIMARY KEY', 'FOREIGN KEY')
AND DATA_TYPE LIKE '%char%'
AND TC.TABLE_SCHEMA + '.' + TC.TABLE_NAME <> 'dbo.dtproperties'

Cordialement tout ça tout ça...,

Fred.M.
(non je ne ferai pas ici l'étalage de mon CV)

A +

"Fred BROUARD" a écrit :

Bonjour

Fred.M. a écrit :

Bonjour Laurent, Get this :

Select DISTINCT O.name as "Table", C.name as Champ, T.name as Type
From sysobjects O, syscolumns C, systypes T, sysforeignkeys PFK
WHERE O.id = C.id AND
C.xtype = T.xtype AND
T.xtype IN (175, 239, 167) AND
((O.id = PFK.fkeyid AND C.colid = PFK.fkey) OR
(O.id= PFK.rkeyid AND C.colid=1))

Quelle horeur !!! ;-)

Ne JAMAIS utiliser les tables systèmes. En effet MS se réserve le droit
de les modifier sans préavis.

Utilisez les vues d'information de schéma (norme SQL), les procédures
stockées système ou encore les vues de management (2005), mais évitez
TOUJOURS si possible l'accès direct aux tabkles système.

De plus faites vos jointures dans une clause FROM et pas dans le where !

Voici donc une version correcte :

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
AND TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON CCU.TABLE_CATALOG = C.TABLE_CATALOG
AND CCU.TABLE_SCHEMA = C.TABLE_SCHEMA
AND CCU.TABLE_NAME = C.TABLE_NAME
AND CCU.COLUMN_NAME = C.COLUMN_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND DATA_TYPE LIKE '%char%'
AND TC.TABLE_SCHEMA + '.' + TC.TABLE_NAME <> 'dbo.dtproperties'

A +

je cherche comment faire un script qui me donnerai sur une base sql 2000, la
liste de toutes les tables possédant des clés primaires ou étrangère de type
varchar ou char avec les noms de champs

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************