Blog > Juillet 2014
Lister l'ensemble des colonnes d'une base de données


Voici une requête permettant de rechercher des colonnes à partir d'une partie de leur nom (dans l'exemple, on recherche les colonnes contenant USER) :

SELECT Sc.[name]      AS [schema_name]
     , Ta.[name]      AS [table_name]
     , Co.[name]      AS [column_name]
     , Ty.[name]      AS [data_type_name]
     , CASE WHEN Ty.[name] IN ('nchar', 'nvarchar', 'ntext') THEN Co.[max_length] / 2 ELSE Co.[max_length] END	AS [max_length]
     , CASE WHEN Co.[is_nullable] = 1 THEN 'Null' ELSE 'Not null' END						AS [nullable]
     , CASE WHEN Co.[is_identity] = 1 THEN 'Identity' ELSE '' END						AS [identity]
FROM		    sys.schemas AS Sc	
	INNER JOIN  sys.tables	AS Ta	ON Sc.[schema_id] = Ta.[schema_id] 
	INNER JOIN  sys.columns AS Co	ON Co.[object_id] = Ta.[object_id]
	INNER JOIN  sys.types	AS Ty	ON Ty.[system_type_id] = Co.[system_type_id] AND Ty.[system_type_id] = Ty.[user_type_id]
WHERE Ta.[name] <> 'dtproperties' 
  AND Co.[name] LIKE '%USER%'
ORDER BY Sc.[name] ASC, Ta.[name] ASC, Co.[name] ASC

Ce type de requêtes peut servir lors de la reprise d'une base de données complexe.