Discussion :

[dgreg60]

Bonjour,

Sur les colonnes de mes tables j'ai ajouté des propriétés étendues que je visualise bien en interrogeant en réalisant un select sur sys.extended_properties
Je cherche maintenant à réaliser une requête du type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select MonChamp,MaValeurProprieteEtendueValeurPourMonChamp From MaTable
 
Join (ou autre) sur sys.extended_properties

L'un d'entre vous a-t-il déjà réaliser ce type d'opération ou a-t-il une idée ?

L'objectif est d'avoir une propriété étendu me donnant un niveau de confidentialité sur le champ (même principe que la fonctionnalité Classify Data) et de faire mes requêtes en récupérant l'indicateur afin d'afficher ou non la valeur sur une UI suivant la caractéristique du champ, l'idée serai d'avoir 3 Niveaux : Visible / Visible sur demande (action user dans UI pour voir) / Non visible.

Merci par avance de vos idées sur le sujet.

[Invité]

J'ai les requêtes suivantes dans mes notes. Je te laisse adapter suivant ton besoin

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
--=========================================================
-- Extended Properties list for a table and its columns + drop Extended Properties
-- liste des Extended Properties d'une table et de ses colonnes + suppression des Extended Properties
SELECT SCHEMA_NAME(OBJ.schema_id) AS TABLE_SCHEMA , OBJ.name AS TABLE_NAME
	, COALESCE(COL.COLUMN_NAME,'*** TABLE LEVEL ***') AS COLUMN_NAME, COL.ORDINAL_POSITION, COL.IS_NULLABLE, COL.DATA_TYPE
	, EXT.NAME AS ExtendedPropertyName, EXT.value AS ExtendedPropertyValue
	, 'EXEC sys.sp_dropextendedproperty @name=N'''+EXT.NAME+''' , @level0type=N''SCHEMA'',@level0name=N'''+SCHEMA_NAME(OBJ.schema_id)+''', @level1type=N''TABLE'',@level1name=N'''+OBJ.name+''''
	+ CASE WHEN COL.COLUMN_NAME IS NULL THEN '' ELSE ', @level2type=N''COLUMN'',@level2name=N'''+COL.COLUMN_NAME+'''' END as DropExtendedProperties
FROM sys.objects AS OBJ
INNER JOIN sys.extended_properties AS EXT ON EXT.major_id = OBJ.object_id AND EXT.class = 1 -- OBJECT_OR_COLUMN
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS AS COL 
	ON COL.TABLE_SCHEMA = SCHEMA_NAME(OBJ.schema_id) AND COL.TABLE_NAME = OBJ.name AND COL.ORDINAL_POSITION = EXT.minor_id
WHERE OBJ.schema_id = SCHEMA_ID('dbo') AND OBJ.name = 'MaTable'
ORDER BY COL.ORDINAL_POSITION, EXT.NAME
 
--=========================================================
-- LISTE DES EXTENDED PROPERTIES SUR TOUS LES TABLES ET COLONNES D'UNE BASE DE DONNÉES
SELECT
	SCHEMA_NAME(tbl.schema_id) AS SchemaName,	
	tbl.name AS TableName, 
	clmns.name AS ColumnName,
	p.name AS ExtendedPropertyName,
	CAST(p.value AS sql_variant) AS ExtendedPropertyValue
FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=clmns.column_id AND p.class=1
 
--=========================================================
-- LISTE DES EXTENDED PROPERTIES SUR TOUS LES OBJETS D'UNE BASE DE DONNÉES
SELECT --objects AND columns
 CASE WHEN ob.parent_object_id>0 THEN OBJECT_SCHEMA_NAME(ob.parent_object_id)+'.'+OBJECT_NAME(ob.parent_object_id)+'.'+ob.name ELSE OBJECT_SCHEMA_NAME(ob.object_id)+'.'+ob.name END+CASE WHEN ep.minor_id>0 THEN '.'+col.name ELSE '' END AS path
	, 'schema'+CASE WHEN ob.parent_object_id>0 THEN '/table' ELSE '' END+'/'
		+CASE WHEN ob.type IN ('TF', 'FN', 'IF', 'FS', 'FT') THEN 'function'
			WHEN ob.type IN ('P', 'PC', 'RF', 'X') THEN 'procedure'
			WHEN ob.type IN ('U', 'IT') THEN 'table'
			WHEN ob.type='SQ' THEN 'queue' ELSE LOWER(ob.type_desc)END+CASE WHEN col.column_id IS NULL THEN '' 
			ELSE '/column' END AS thing
	, ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.objects ob ON ep.major_id=ob.object_id AND class=1
LEFT OUTER JOIN sys.columns col ON ep.major_id=col.object_id AND class=1 AND ep.minor_id=col.column_id
UNION ALL SELECT --indexes
 OBJECT_SCHEMA_NAME(ob.object_id)+'.'+OBJECT_NAME(ob.object_id)+'.'+ix.name, 'schema/'+LOWER(ob.type_desc)+'/index', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.objects ob ON ep.major_id=ob.object_id AND class=7
INNER JOIN sys.indexes ix ON ep.major_id=ix.object_id AND class=7 AND ep.minor_id=ix.index_id
UNION ALL SELECT --Parameters
 OBJECT_SCHEMA_NAME(ob.object_id)+'.'+OBJECT_NAME(ob.object_id)+'.'+par.name, 'schema/'+LOWER(ob.type_desc)+'/parameter', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.objects ob ON ep.major_id=ob.object_id AND class=2
INNER JOIN sys.parameters par ON ep.major_id=par.object_id AND class=2 AND ep.minor_id=par.parameter_id
UNION ALL SELECT --schemas
 sch.name, 'schema', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.schemas sch ON class=3 AND ep.major_id=schema_id
UNION ALL --Database 
SELECT DB_NAME(), 'database', ep.name, value
FROM sys.extended_properties ep
WHERE class=0
UNION ALL --XML Schema Collections
SELECT SCHEMA_NAME(schema_id)+'.'+xc.name, 'schema/xml_Schema_collection', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.xml_schema_collections xc ON class=10 AND ep.major_id=xml_collection_id
UNION ALL
SELECT --Database Files
 df.name, 'database_file', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.database_files df ON class=22 AND ep.major_id=file_id
UNION ALL
SELECT --Data Spaces
 ds.name, 'dataspace', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.data_spaces ds ON class=20 AND ep.major_id=data_space_id
UNION ALL
SELECT --USER
 dp.name, 'database_principal', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.database_principals dp ON class=4 AND ep.major_id=dp.principal_id
UNION ALL
SELECT --PARTITION FUNCTION
 pf.name, 'partition_function', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.partition_functions pf ON class=21 AND ep.major_id=pf.function_id
UNION ALL
SELECT --REMOTE SERVICE BINDING
 rsb.name, 'remote service binding', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.remote_service_bindings rsb ON class=18 AND ep.major_id=rsb.remote_service_binding_id
UNION ALL
SELECT --Route
 rt.name, 'route', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.routes rt ON class=19 AND ep.major_id=rt.route_id
UNION ALL
SELECT --Service
 sv.name COLLATE DATABASE_DEFAULT, 'service', ep.name, value
FROM sys.extended_properties ep
INNER JOIN sys.services sv ON class=17 AND ep.major_id=sv.service_id
UNION ALL
SELECT -- 'CONTRACT'
 svc.name, 'service_contract', ep.name, value
FROM sys.service_contracts svc
INNER JOIN sys.extended_properties ep ON class=16 AND ep.major_id=svc.service_contract_id
UNION ALL
SELECT -- 'MESSAGE TYPE'
 smt.name, 'message_type', ep.name, value
FROM sys.service_message_types smt
INNER JOIN sys.extended_properties ep ON class=15 AND ep.major_id=smt.message_type_id
UNION ALL
SELECT -- 'assembly'
 asy.name, 'assembly', ep.name, value
FROM sys.assemblies asy
INNER JOIN sys.extended_properties ep ON class=5 AND ep.major_id=asy.assembly_id
/*UNION ALL SELECT --'CERTIFICATE'
 cer.name,'certificate', ep.name,value FROM sys.certificates cer INNER JOIN sys.extended_properties ep ON class=? AND ep.major_id=cer.certificate_id
UNION ALL SELECT --'ASYMMETRIC KEY'
 amk.name,'asymmetric_key', ep.name,value SELECT * from sys.asymmetric_keys amk INNER JOIN sys.extended_properties ep ON class=? AND ep.major_id=amk.asymmetric_key_id
SELECT --'SYMMETRIC KEY'
 smk.name,'symmetric_key', ep.name,value from sys.symmetric_keys smk
INNER JOIN sys.services sv ON class=? AND ep.major_id=smk.symmetric_key_id */
UNION ALL
SELECT -- 'PLAN GUIDE' 
 pg.name, 'plan_guide', ep.name, value
FROM sys.plan_guides pg
INNER JOIN sys.extended_properties ep ON class=27 AND ep.major_id=pg.plan_guide_id;

[dgreg60]

Merci pour tes requêtes mais cela ne correspond pas exactement à mon besoin.

Les requêtes travaille uniquement sur les propriétés étendue, ce que je voudrais faire c'est affiché la valeur de la propriété étendu dans une requête de sélection de données.

[dbnsql]

Bonsoir,

Ça me semble pourtant pas mal du tout.

7gyY9w1ZY6ySRgPeaefZ: Je te laisse adapter suivant ton besoin

Donc à adapter, avec une fonction scalaire comme ci dessous, une fonction de table, une jointure.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
CREATE FUNCTION fnGetExtendedProperyValue(@table_schema SYSNAME, @table_name SYSNAME, @column_name SYSNAME, @extendedpropertyname SYSNAME)
RETURNS sql_variant
AS
BEGIN
	RETURN (SELECT EXT.value AS ExtendedPropertyValue
			FROM sys.objects AS OBJ
			INNER JOIN sys.extended_properties AS EXT ON EXT.major_id = OBJ.object_id AND EXT.class = 1 -- OBJECT_OR_COLUMN
			LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS AS COL 
				ON COL.TABLE_SCHEMA = SCHEMA_NAME(OBJ.schema_id) AND COL.TABLE_NAME = OBJ.name AND COL.ORDINAL_POSITION = EXT.minor_id
			WHERE 
				OBJ.schema_id = SCHEMA_ID(@table_schema) AND OBJ.name = @table_name and COL.COLUMN_NAME = @column_name and EXT.NAME = @extendedpropertyname
)
END
 
-- schema dbo, table titi, colonne a, extended property MS_Description
SELECT a, dbo.fnGetExtendedProperyValue('dbo', 'titi', 'a',  'MS_Description') FROM dbo.titi
 
EXEC sp_addextendedproperty  
     @name = N'myep' 
    ,@value = N'Test' 
    ,@level0type = N'Schema', @level0name = 'dbo' 
    ,@level1type = N'Table',  @level1name = 'titi' 
    ,@level2type = N'Column', @level2name = 'a'
 
-- schema dbo, table titi, colonne a, extended property myep
SELECT a, dbo.fnGetExtendedProperyValue('dbo', 'titi', 'a',  'myep') FROM dbo.titi

Have fun