Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

MS SQL Server Discussion :

T-SQL / Select & Propriété etendue d'un champ


Sujet :

MS SQL Server

  1. #1
    Nouveau Candidat au Club
    Homme Profil pro
    Responsable de service informatique
    Inscrit en
    juillet 2009
    Messages
    2
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Yonne (Bourgogne)

    Informations professionnelles :
    Activité : Responsable de service informatique
    Secteur : Finance

    Informations forums :
    Inscription : juillet 2009
    Messages : 2
    Points : 1
    Points
    1
    Par défaut T-SQL / Select & Propriété etendue d'un champ
    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.

  2. #2
    Expert éminent Avatar de 7gyY9w1ZY6ySRgPeaefZ
    Homme Profil pro
    dba
    Inscrit en
    juillet 2007
    Messages
    4 861
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : juillet 2007
    Messages : 4 861
    Points : 6 262
    Points
    6 262
    Par défaut
    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;

  3. #3
    Nouveau Candidat au Club
    Homme Profil pro
    Responsable de service informatique
    Inscrit en
    juillet 2009
    Messages
    2
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Yonne (Bourgogne)

    Informations professionnelles :
    Activité : Responsable de service informatique
    Secteur : Finance

    Informations forums :
    Inscription : juillet 2009
    Messages : 2
    Points : 1
    Points
    1
    Par défaut
    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.

  4. #4
    Nouveau membre du Club
    Homme Profil pro
    none
    Inscrit en
    janvier 2020
    Messages
    16
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : none

    Informations forums :
    Inscription : janvier 2020
    Messages : 16
    Points : 25
    Points
    25
    Par défaut
    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

Discussions similaires

  1. [SQL] SELECT + EXCEPT
    Par Mike@Nestor dans le forum Langage SQL
    Réponses: 18
    Dernier message: 23/08/2005, 17h07
  2. [SQL] select en JAVA
    Par webbulls dans le forum JDBC
    Réponses: 2
    Dernier message: 09/06/2005, 15h08
  3. [SQL] selection des dates en ne distinguant pas l'heure
    Par meufeu dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 01/06/2005, 12h29
  4. [SQL] select syntaxe
    Par Leila75 dans le forum Oracle
    Réponses: 3
    Dernier message: 10/05/2005, 12h05
  5. SQL :select et AND
    Par Shabata dans le forum Langage SQL
    Réponses: 5
    Dernier message: 20/05/2003, 14h39

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo