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; |
Partager