Utilisation des propriétés étendues de SQL Server – Serveur d’impression

Par: Jayendra Viswanathan | Dernière mise à jour: 2018-04-13 | Commentaires (1) | Conseils connexes: Plus> Conception de base de données

Problème

SQL Server peut stocker un grand volume de données dans des formats relationnels, ce qui est excellent
pour les entreprises, mais les utilisateurs et les développeurs ont également besoin de stocker de la documentation
et des informations liées aux objets SQL Server. Une façon de faire est d'utiliser
Propriétés étendues qui vous permet d’enregistrer des informations sur les objets tels que
comme ce à quoi il sert, des formats spécifiques comme le format du téléphone, le format de la date, la description de
objets, URL, liens de sites Web, etc. Dans cette astuce, nous montrerons comment nous pouvons ajouter
Propriétés étendues.

Solution

Propriétés étendues est une fonctionnalité unique dans SQL Server pour stocker plus d'informations
sur les objets de base de données. Dans cet article, nous verrons comment:

1. Ajouter, mettre à jour et supprimer des propriétés étendues.
2. Extrayez les propriétés étendues de sys.objects et de sys.extended_properties
   les tables.
3. Comment utiliser la fonction FN_LISTEXTENDEDPROPERTY () pour extraire les propriétés étendues.

Des propriétés étendues peuvent être créées pour les objets de base de données ci-dessous, mais
astuce, nous allons nous concentrer sur les propriétés étendues au niveau des colonnes.

• Base de données
• Procédures stockées
• Fonctions définies par l'utilisateur
• Table
• Colonne de table
• Index de table
• Des vues
• Règles
• Déclencheurs
• Contraintes

Les propriétés étendues peuvent être utilisées pour:

• Spécifiez une légende pour une table, une vue ou une colonne.
• Spécifiez un masque d'affichage pour une colonne.
• Afficher le format d’une colonne, définir le masque de modification pour une colonne de date, définir
  nombre de décimales, etc.
• Spécifiez les règles de formatage pour afficher les données dans une colonne.
• Décrire un objet de base de données spécifique pour tous les utilisateurs.

Exemple de création de propriétés étendues

Créons une table composée de deux colonnes «sno» et «myName».

SI OBJECT_ID ('MyTest', 'U') N'EST PAS NUL
    DROP TABLE MyTest;
ALLER

SET ANSI_NULLS ON
ALLER
SET QUOTED_IDENTIFIER ON
ALLER
CREATE TABLE MyTest (sno int, myName char (20))
ALLER	

Pour examiner les propriétés étendues d’une colonne, dans SSMS, développez Tables, recherchez
la table que nous avons créée, puis développons les colonnes. Puis clic droit sur la colonne "sno"
et sélectionnez Propriétés et accédez à la page Propriétés étendues.

La capture d'écran ci-dessous montre les propriétés étendues de la colonne "sno"
est vide après la création de la table. Si vous voulez ajouter une propriété étendue, vous devez
pouvez simplement taper le nom et la valeur sur l’écran ci-dessous. Je vais aussi montrer
comment faire cela avec T-SQL.

Ajouter une propriété étendue SQL Server avec sp_addextendedproperty

Nous pouvons également utiliser sp_addextendedproperty pour ajouter une propriété étendue. Le dessous
procédure stockée doit être exécuté avec les paramètres suivants.

exec sp_addextendedproperty
     @name = N'SNO '
    , @ valeur = N'Tester l'entrée pour la propriété étendue '
    , @ level0type = N'Schema ', @ level0name =' dbo '
    , @ level1type = N'Table ', @ level1name =' mytest '
    , @ level2type = N'Column ', @ level2name =' sno '
aller			

Quelques paramètres sont nécessaires pour exécuter sp_addextendedproperty.

• @name est ‘SNO’ dans notre cas. Cela ne peut pas être nul. C'est le
  nom de la propriété étendue.
• @value est la valeur ou la description de la propriété et ne peut dépasser
  7500 octets.
• @ level0type dans notre cas ‘Schema’ et @ level0name est la valeur
  est défini comme "dbo" comme valeur
• @ level1type dans notre cas 'Table' et @ level1name est 'mytest'
• @ level2type dans notre cas 'Column' et @ level2name est 'sno'

L'écran ci-dessous montre la propriété étendue ajoutée à l'aide de sp_addextendedproperty.
Il affiche le nom et la valeur de la propriété étendue.

Interrogation des propriétés étendues de SQL Server

Sp_addextendedproperty créera des lignes dans la table sys.extended_properties,
en stockant des données dans cette table, SQL Server a la possibilité de récupérer les données conformément à
la demande. Dans de nombreux projets d’automatisation de la documentation, ces tables peuvent être interrogées.
et les données peuvent être utilisées pour la documentation fins.

Ci-dessous se trouve la requête pour extraire des données sur la table ‘MyTest’. nous
peut voir la valeur object_id pour la table.

sélectionnez *
de sys.tables
où name = 'MyTest'			

Ci-dessous, nous pouvons interroger sys.extended_properties pour obtenir plus d'informations. Nous pouvons aussi
voir le major_id correspond au object_id ci-dessus.

sélectionnez *
à partir de sys.extended_properties
où NAME = 'SNO'		

Si nous courons un

Trace SQL Server nous pouvons capturer ce que SQL Server utilise dans
SSMS, on trouve ce qui suit
SSMS utilise query pour extraire les données des propriétés étendues au niveau des colonnes. le
Le script ci-dessous provient d'une instance de SQL Server 2017.

exec sp_executesql N'SELECT
p.name AS [Name],
CAST (p.value AS sql_variant) AS [Value]
DE
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
OÙ
([email protected]_msparam_0) et (([email protected]_msparam_1) et (([email protected]_msparam_2 et SCHEMA_NAME (tbl.schema_id)[email protected]_msparam_3)))
OPTION (ORDRE DE FORCE)
', N' @ _ msparam_0 Nvarchar (4000), @ _ ​​msparam_1 Nvarchar (4000), @ _ ​​msparam_2 Nvarchar (4000), @ _ ​​msparam_3 Nvarchar (4000) ', @ _ ​​msparam_0 = N'SNO',
@ _msparam_1 = N'sno ', @ _ ​​msparam_2 = N'MyTest', @ _ ​​msparam_3 = N'dbo '

Nous pouvons simplifier cela et utiliser la requête suivante pour obtenir la propriété étendue
pour la colonne. Ceci a été testé sur SQL 2012, 2014, 2016 et 2017, ainsi que sur tous
des autres requêtes ci-dessous.

SÉLECTIONNER
   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
DE
   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
OÙ
   SCHEMA_NAME (tbl.schema_id) = 'dbo'
   et tbl.name = 'MyTest'
   et clmns.name = 'sno'
   et p.name = 'SNO'
      

Voici la sortie.

Obtenir toutes les propriétés étendues au niveau de la colonne

Si nous voulons obtenir des propriétés étendues au niveau des colonnes pour toutes les colonnes de la base de données,
nous pourrions courir le suivant.

SÉLECTIONNER
   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
DE
   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
      

Voici d'autres exemples de requêtes.

Obtenir toutes les propriétés étendues de niveau base de données

SÉLECTIONNER
   DB_NAME () AS DatabaseName,
   p.name AS ExtendedPropertyName,
   p.value AS ExtendedPropertyValue
DE
   sys.extended_properties AS p
OÙ
   p.major_id = 0
   AND p.minor_id = 0
   AND p.class = 0
COMMANDÉ PAR
   [Name] ASC

Obtenir toutes les propriétés étendues au niveau de la table

SÉLECTIONNER
   SCHEMA_NAME (tbl.schema_id) AS SchemaName,
   tbl.name AS TableName,
   p.name AS ExtendedPropertyName,
   CAST (p.value AS sql_variant) AS ExtendedPropertyValue
DE
   sys.tables AS tbl
   INNER JOIN sys.extended_properties AS p ON p.major_id = tbl.object_id AND p.minor_id = 0 AND p.class = 1

Obtenir toutes les propriétés étendues de procédure stockée

SÉLECTIONNER
   SCHEMA_NAME (sp.schema_id) AS SchemaName,
   sp.name AS SPName,
   p.name AS ExtendedPropertyName,
   CAST (p.value AS sql_variant) AS ExtendedPropertyValue
DE
   sys.all_objects AS sp
   INNER JOIN sys.extended_properties AS p ON p.major_id = sp.object_id AND p.minor_id = 0 AND p.class = 1
OÙ
   sp.type = 'P' OU sp.type = 'RF' OU sp.type = 'PC'

Obtenir une propriété étendue à l'aide de fn_listextendedproperty

Nous pouvons également utiliser la fonction fn_listextendedproperty pour obtenir une liste des propriétés étendues.
La fonction retourne type d'objet, nom d'objet, nom de type de données type sysname et valeur sous la forme sql_varient.
NULL peut être utilisé comme paramètre pour que le nom d’objet renvoie plusieurs noms étendus.
propriétés, mais il est pas aussi simple que vous le pensez il devrait être, pour
utiliser cette fonction.

La fonction se trouve dans la base de données master sous Programmabilité> Fonctions>
Fonctions du système.

Exécutez la requête ci-dessous pour obtenir la propriété étendue.

SELECT *
FROM :: fn_listextendedproperty ('SNO', 'Schéma', 'dbo', 'Table', 'mytest', 'Column', 'sno')	

Dans la requête ci-dessus, nous remarquons que le premier paramètre est le nom de propriété «SNO».
et les 6 autres paramètres sont le niveau d'objet et le type d'objet du niveau 0 à 2
notre exemple. Ci-dessous la sortie:

Mettre à jour une propriété étendue avec sp_updateextendedproperty

Nous pouvons utiliser la procédure stockée sp_updateextendedproperty pour mettre à jour la valeur de
propriété étendue existante.

exec sp_updateextendedproperty
     @name = N'SNO '
    , @ value = 'L'ID SNO doit être unique.'
    , @ level0type = N'Schema ', @ level0name =' dbo '
    , @ level1type = N'Table ', @ level1name =' mytest '
    , @ level2type = N'Column ', @ level2name =' sno '
ALLER			

La procédure stockée de mise à jour est utilisée pour mettre à jour la valeur de la propriété étendue en tant que «SNO
L'identifiant doit être unique ». La procédure stockée de mise à jour est similaire à la procédure d'ajout
procédure stockée qui accepte la même
paramètres et sa récupère la ligne en fonction de la propriété Name qui est 'SNO'
dans notre exemple.

La copie d'écran ci-dessous montre le texte mis à jour dans notre exemple.

Nous pouvons interroger à nouveau les données en utilisant la fonction pour montrer le changement.

SELECT *
FROM :: fn_listextendedproperty ('SNO', 'Schéma', 'dbo', 'Table', 'mytest', 'Column', 'sno')	

Supprimer la propriété étendue avec la propriété sp_dropextended

La propriété sp_dropextended supprime une propriété étendue de la base de données. le
Ce qui suit montre comment supprimer une entrée.

exec sp_dropextendedproperty @ name = N'SNO '
  , @ level0type = N'Schema ', @ level0name =' dbo '
  , @ level1type = N'Table ', @ level1name =' mytest '
  , @ level2type = N'Column ', @ level2name =' sno '
aller			

Nous pouvons interroger à nouveau les données en utilisant la fonction pour montrer que l'entrée a été supprimée.

SELECT *
FROM :: fn_listextendedproperty ('SNO', 'Schéma', 'dbo', 'Table', 'mytest', 'Column', 'sno')
			

Conclusion

Nous avons vu comment ajouter, mettre à jour et supprimer des propriétés étendues dans SQL Server.
Nous avons également vu comment fn_listextendedproperty peut être utilisé pour interroger le fichier étendu étendu disponible.
Propriétés.

Les propriétés étendues sont une fonctionnalité utile dans SQL Server qui peut être utilisée pour
la documentation et le contenu. Les propriétés peuvent être mises à jour pour les tables, vues,
déclencheurs et ainsi de suite. Les développeurs peuvent utiliser cette fonctionnalité pour des objets de base de données étendus.
qui peut être utilisé comme référence pour de nombreux objets SQL Server.

Prochaines étapes

• Des détails sur les vues de catalogue système SQL Server sont disponibles.
  
  ici.

Dernière mise à jour: 2018-04-13

A propos de l'auteur

Jayendra est un chef de projet avec plusieurs années d'expérience en informatique. Il possède de solides connaissances en développement de logiciels et en gestion de projets.

Voir tous mes conseils