Lister les types définis par l’utilisateur, avec les défauts et règles qui leur sont liés, et les scripter

Publié le par

Voici une petite requête qui permet de retrouver la liste de tous les types définis par l’utilisateur …

Comme tout type créé par l’utilisateur est une dérivation d’un type de base, les valeurs de la colonne user_type_id dans la vue sys.types progressent tandis que celles de la colonne system_type_id restent limitées au nombre de types « de base » livrés avec l’instance SQL Server.

Dès lors la requête suivante :

1
2
3
4
5
6
7
-------------------------------
-- 09/02/2009 - Nicolas SOUQUET
-------------------------------
SELECT name
FROM sys.types
WHERE user_type_id > system_type_id
ORDER BY name

Retourne la liste de tous les types définis par l’utilisateur.
Néanmoins on peut en exclure sysname qui est une dérivation du type NVARCHAR pour les noms d’objets.

Si l’on souhaite trouver le nom et la définition T-SQL des défauts et règles qui sont liés (respectivement par sp_binddefault et sp_bindrule) à ces types, il faudra exécuter :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-------------------------------
-- 09/02/2010 - Nicolas SOUQUET
-------------------------------
SELECT T.name AS nomType
  , D.name AS nomDefaut
  , DD.definition AS defautSQL
  , R.name AS nomRegle
  , DR.definition AS regleSQL
FROM sys.types AS T
LEFT JOIN sys.objects AS D
  ON D.object_id = T.default_object_id
LEFT JOIN sys.sql_modules AS DD
  ON D.object_id = DD.object_id
LEFT JOIN sys.objects AS R
  ON R.object_id = T.rule_object_id
LEFT JOIN sys.sql_modules AS DR
  ON D.object_id = DR.object_id
WHERE T.user_type_id > T.system_type_id
--AND T.name <> 'sysname'
ORDER BY T.name

On peut mettre en parallèle les types définis par l’utilisateur avec les types de base :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-------------------------------
-- 09/02/2010 - Nicolas SOUQUET
-------------------------------
SELECT T.name AS table_name
  , C.name AS column_name
  , TY.name AS user_type_name
  , TYO.name AS type_name
  , CASE
    WHEN TYO.name IN ('nchar', 'nvarchar') THEN C.max_length / 2
    ELSE C.max_length
  END AS max_length
  , C.max_length AS nbBytes
FROM sys.tables AS T
JOIN sys.columns AS C
  ON T.object_id = C.object_id
JOIN sys.types AS TY
  ON C.user_type_id = TY.user_type_id
JOIN sys.types AS TYO
  ON TY.system_type_id = TYO.system_type_id
  AND TYO.user_type_id = TYO.system_type_id
WHERE T.name = 'radiology_procedure_plan_ref'
ORDER BY C.column_id

Si l’on souhaite scripter la liaison d’un défaut à un type, nous exécuterons :

1
2
3
4
5
6
7
-------------------------------
-- 09/02/2010 - Nicolas SOUQUET
-------------------------------
SELECT 'EXEC sp_bindefault @defname = ''' + D.name + '''' + ', @objname = ''' + T.name + ''''
FROM sys.types AS T
JOIN sys.objects AS D
  ON D.object_id = T.default_object_id

Et similairement, pour scripter la liaison d’une règle à un type :

1
2
3
4
5
6
7
-------------------------------
-- 09/02/2010 - Nicolas SOUQUET
-------------------------------
SELECT 'EXEC sp_bindrule @rulename = ''' + R.name + '''' + ', @objname = ''' + T.name + ''''
FROM sys.types AS T
JOIN sys.objects AS R
  ON R.object_id = T.rule_object_id

Bon typage ! ;)

ElSuket.

Laisser un commentaire