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