On a besoin *parfois* de connaitre ou avoir la liste de toutes les tables et les colonnes
Ce script crée une table avec toutes définitions.
ex:
1
2select * from _text_table_dictionary
3where Datatype = 'datetime'
1
2PRINT @@servername;
3GO
4
5IF EXISTS (
6 SELECT *
7 FROM dbo.sysobjects
8 WHERE id = object_id(N'[dbo].[_text_table_dictionary]')
9 AND OBJECTPROPERTY(id, N'IsUserTable') = 1
10 )
11 DROP TABLE [dbo].[_text_table_dictionary]
12GO
13
14CREATE TABLE [dbo].[_text_table_dictionary] ([RowID] [int] IDENTITY(1, 1) NOT NULL, [TableName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TableSchema] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ColumnName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrdinalPosition] [int] NULL, [ColumnDefault] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [IsNullable] [bit] NULL, [DataType] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CharacterMaximumLength] [int] NULL, [CharacterMaximumUsed] [int] NULL, [RecordCount] [int] NULL, [IsIdentity] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [IsPrimaryKey] [bit] NULL, [IsForeignKey] [bit] NULL, [IsComputed] [bit] NULL, [ReferencedTableName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ReferencedColumnName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ObjectDescription] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [temp] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) ON [PRIMARY]
15GO
16
17DECLARE @DataDictionary TABLE (
18 RowID [int] identity(1, 1) PRIMARY KEY, TableName [varchar](200), TableSchema [varchar](200), ColumnName [varchar](200), OrdinalPosition INT, ColumnDefault [varchar](200), IsNullable [bit], DataType [varchar](20), CharacterMaximumLength INT, IsIdentity [varchar](5) --[bit]
19 , IsPrimaryKey [bit], IsForeignKey [bit], IsComputed [bit], ReferencedTableName [varchar](200), ReferencedColumnName [varchar](200), ObjectDescription [varchar](500)
20 )
21DECLARE @TableList TABLE (RowID [int] identity(1, 1) PRIMARY KEY, TableCatalog [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS, TableSchema [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS, TableName [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS, TableType [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS, ObjectDescription [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS)
22DECLARE @RowNumber INT, @RowCount INT, @TableName VARCHAR(255), @TableSchema VARCHAR(255), @ObjectDescription VARCHAR(500)
23
24--create a temp table to hold the primary keys
25--can't do INSERT EXEC with a table variable
26IF object_id('tempdb..#PkColumns') IS NOT NULL
27BEGIN
28 DROP TABLE #PkColumns
29END
30
31CREATE TABLE #PkColumns (RowID [int] identity(1, 1) PRIMARY KEY, table_Qualifier VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, owner_name VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, table_name VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, column_name VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, key_seq VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, Pk_Name VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL)
32
33--load the table list
34INSERT @TableList (TableCatalog, TableSchema, TableName, TableType, ObjectDescription)
35SELECT t.Table_Catalog, t.Table_Schema, t.Table_Name, t.Table_Type, CAST(IsNULL(e.value, '') AS VARCHAR(500))
36FROM INFORMATION_SCHEMA.TABLES t
37LEFT JOIN::FN_LISTEXTENDEDPROPERTY('MS_Description' --standard description property
38 , 'user', 'dbo', 'table' --parent object type
39 , @TableName -- parent object name
40 , NULL --child object type
41 , NULL --child object name from above
42 ) e ON t.Table_Name = (cast(e.objname AS VARCHAR(255)) COLLATE SQL_Latin1_General_CP1_CI_AS)
43WHERE t.Table_Type = 'BASE TABLE'
44
45SELECT @RowCount = Count(*)
46FROM @TableList
47
48--loop through the tables
49SET @RowNumber = 1
50
51WHILE @RowNumber <= @RowCount
52BEGIN
53 SELECT @TableName = TableName, @TableSchema = TableSchema, @ObjectDescription = ObjectDescription
54 FROM @TableList
55 WHERE RowID = @RowNumber
56
57 --clear primary keys table
58 DELETE #PkColumns
59
60 --insert the primary key records retrieved by the system stored procedure
61 INSERT #PkColumns
62 EXEC sp_pkeys @table_name = @TableName
63
64 --insert the table name and description for header purposes
65 INSERT @DataDictionary (TableName, ColumnName, OrdinalPosition, ColumnDefault, IsNullable, DataType, CharacterMaximumLength, IsIdentity, IsPrimaryKey, IsForeignKey, IsComputed, ReferencedTableName, ReferencedColumnName, ObjectDescription, TableSchema)
66 VALUES (
67 @TableName --TableName
68 , '' --ColumnName
69 , 0 --OrdinalPosition
70 , '' --ColumnDefault
71 , 0 --IsNullable
72 , '' --DataType
73 , 0 --CharacterMaximumLength
74 , 0 --IsIdentity
75 , 0 --IsPrimaryKey
76 , 0 --IsForeignKey
77 , 0 --IsComputed
78 , '' --ReferencedTableName
79 , '' --ReferencedColumnName
80 , @ObjectDescription --ObjectDescription
81 , @TableSchema
82 )
83
84 --insert the column schema information
85 INSERT @DataDictionary (TableName, TableSchema, ColumnName, OrdinalPosition, ColumnDefault, IsNullable, DataType, CharacterMaximumLength, IsIdentity, IsPrimaryKey, IsForeignKey, IsComputed, ReferencedTableName, ReferencedColumnName, ObjectDescription)
86 SELECT t.table_name, t.table_schema, c.column_name, c.ordinal_position, IsNULL(c.column_default, ''), CASE WHEN c.is_nullable = 'Yes' THEN 1 ELSE 0 END, c.data_type, IsNULL(c.character_maximum_length, ''), (
87 SELECT COLUMNPROPERTY(OBJECT_ID(t.table_name), c.Column_Name, 'IsIdentity')
88 ) AS IsIdentity, CASE WHEN pk.column_name IS NULL THEN 0 ELSE 1 END AS IsPrimaryKey, CASE WHEN Fkey.REFERENCED_TABLE_NAME IS NULL THEN 0 ELSE 1 END AS IsForeignKey, CASE WHEN Fkey.REFERENCED_TABLE_NAME IS NULL THEN 0 ELSE 1 END AS IsComputed, IsNULL(Fkey.REFERENCED_TABLE_NAME, ''), IsNULL(Fkey.Referenced_Column_Name, ''), CAST(IsNULL(e.value, '') AS VARCHAR(500)) COLLATE SQL_Latin1_General_CP1_CI_AS AS 'ColumnDescription'
89 FROM information_schema.tables t
90 INNER JOIN information_schema.columns C ON t.table_name = c.table_name
91 LEFT OUTER JOIN [#PkColumns] Pk ON PK.column_Name = c.column_Name
92 LEFT OUTER JOIN (
93 SELECT CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'Enabled' ELSE 'Disabled' END AS STATUS, OBJECT_NAME(CONSTID) AS Constraint_Name, OBJECT_NAME(FKEYID) AS Table_Name, COL_NAME(FKEYID, FKEY) AS Column_Name, OBJECT_NAME(RKEYID) AS Referenced_Table_Name, COL_NAME(RKEYID, RKEY) AS Referenced_Column_Name
94 FROM SYSFOREIGNKEYS
95 ) AS Fkey ON c.table_name = Fkey.table_name
96 AND c.column_Name = Fkey.Column_Name
97 LEFT JOIN::FN_LISTEXTENDEDPROPERTY('MS_Description' --standard description property
98 , 'user', 'dbo', 'table' --parent object type
99 , @TableName -- parent object name from above
100 , 'column' -- child object type
101 , NULL -- child object name from above
102 ) e ON c.Column_Name = e.objname COLLATE SQL_Latin1_General_CP1_CI_AS
103 WHERE t.table_name = @TableName
104 ORDER BY c.ordinal_position
105
106 SET @RowNumber = @RowNumber + 1
107END --end table loop
108
109--drop the temp table
110DROP TABLE #PkColumns
111
112/*
113SELECT *
114FROM
115@DataDictionary d
116
117ORDER BY
118d.TableName
119, d.OrdinalPosition
120*/
121DELETE
122FROM _text_table_dictionary
123
124INSERT INTO _text_table_dictionary (TableName, TableSchema, ColumnName, OrdinalPosition, ColumnDefault, IsNullable, DataType, CharacterMaximumLength, IsIdentity, IsPrimaryKey, IsForeignKey, ReferencedTableName, ReferencedColumnName, ObjectDescription)
125SELECT TableName, TableSchema, ColumnName, OrdinalPosition, ColumnDefault, IsNullable, DataType, CharacterMaximumLength, IsIdentity, IsPrimaryKey, IsForeignKey, ReferencedTableName, ReferencedColumnName, ObjectDescription
126FROM @DataDictionary d
127GO
128
129SELECT *
130FROM _text_table_dictionary
131
132SELECT DISTINCT TableName
133FROM _text_table_dictionary
134WHERE RecordCount > 0
135 --truncate table Audit
136 --truncate table AuditConfiguration
137 --Update _text_table_dictionary set RecordCount = ( select Count(*) as n from dbo.[User] ) Where TableName='User'
138GO
139
140-- Length Field
141BEGIN TRY
142 DROP PROC sx_ColumnCharacterMaximumUsed
143END TRY
144
145BEGIN CATCH
146END CATCH
147GO
148
149CREATE PROC sx_ColumnCharacterMaximumUsed
150AS
151BEGIN
152 DECLARE @TableName VARCHAR(200)
153 DECLARE @TableSchema VARCHAR(200)
154 DECLARE @ColumnName VARCHAR(200)
155 DECLARE @Sql VARCHAR(1250)
156 DECLARE @n INTEGER
157
158 DECLARE table_cursor CURSOR
159 FOR
160 SELECT TableName, ColumnName, TableSchema
161 FROM _text_table_dictionary
162 WHERE DataType IN ('varchar', 'char', 'nvarchar')
163 AND CharacterMaximumLength > 1000
164
165 UPDATE _text_table_dictionary
166 SET CharacterMaximumUsed = - 1
167
168 OPEN table_cursor
169
170 FETCH NEXT
171 FROM table_cursor
172 INTO @TableName, @ColumnName, @TableSchema
173
174 WHILE @@FETCH_STATUS = 0
175 BEGIN
176 PRINT '----------------------------'
177 PRINT 'Table Name Columns: [' + @TableName + '] -> [' + @ColumnName + ']'
178
179 SET @sql = ''
180 SET @sql = @sql + ' Update _text_table_dictionary'
181 SET @sql = @sql + ' set CharacterMaximumUsed = ('
182 SET @sql = @sql + ' select MAX(Len(' + @ColumnName + ')) as n '
183 SET @sql = @sql + ' from ' + @TableSchema + '.[' + @TableName + '])'
184 SET @sql = @sql + ' Where'
185 SET @sql = @sql + ' ColumnName = ''' + @ColumnName + ''' '
186 SET @sql = @sql + ' and TableName= ''' + @TableName + ''' '
187
188 PRINT @sql
189
190 EXEC (@sql)
191
192 --Select @Sql = 'Select Count(*) as n from ' + @TableName
193 FETCH NEXT
194 FROM table_cursor
195 INTO @TableName, @ColumnName, @TableSchema
196 END
197
198 CLOSE table_cursor
199
200 DEALLOCATE table_cursor
201
202 -- Record Count
203 UPDATE _text_table_dictionary
204 SET RecordCount = 0
205
206 DECLARE table_cursor_2 CURSOR
207 FOR
208 SELECT TableName, TableSchema
209 FROM _text_table_dictionary
210 GROUP BY TableName, TableSchema
211
212 OPEN table_cursor_2
213
214 FETCH NEXT
215 FROM table_cursor_2
216 INTO @TableName, @TableSchema
217
218 WHILE @@FETCH_STATUS = 0
219 BEGIN
220 PRINT '-----------------'
221
222 SET @sql = ''
223 SET @sql = @sql + ' Update _text_table_dictionary'
224 SET @sql = @sql + ' set RecordCount = ('
225 SET @sql = @sql + ' select Count(*) as n '
226 SET @sql = @sql + ' from ' + @TableSchema + '.[' + @TableName + '])'
227 SET @sql = @sql + ' Where'
228 SET @sql = @sql + ' TableName=''' + @TableName + ''''
229
230 PRINT 'Table Name Count: ' + @TableName + ' '
231 PRINT @sql
232
233 EXEC (@sql)
234
235 FETCH NEXT
236 FROM table_cursor_2
237 INTO @TableName, @TableSchema
238 END
239
240 CLOSE table_cursor_2
241
242 DEALLOCATE table_cursor_2
243END
244GO
245
246/*
247select * from _text_table_dictionary
248where
249CharacterMaximumLength > 2048 and DataType = 'nvarchar'
250*/
251/*
252select * from _text_table_dictionary
253where ColumnName like '%ID%'
254
255*/
256GO
257
258/*
259select * from _text_table_dictionary
260where DataType = 'char'
261
262*/
263/*
264-- -- -- -- -- -- -- -- --
265--
266-- Create list of columns without timestamp
267--
268-- -- -- -- -- -- -- -- --
269
270drop table #temp1
271go
272SELECT TableName,
273 substring(
274 stuff(
275 ( SELECT distinct ',['+ ColumnName+']'
276 FROM (select distinct * from _text_table_dictionary where NOT ColumnName in ('', 'Timestamp')) t1
277 WHERE t2.TableName = t1.TableName FOR xml path('')
278 )
279 ,1,1,'')
280 ,0,4096) as ColumnList
281into #temp1
282FROM _text_table_dictionary t2
283GROUP BY TableName
284ORDER BY TableName
285go
286
287update _text_table_dictionary set [temp] = ColumnList
288from #temp1, _text_table_dictionary
289where _text_table_dictionary.OrdinalPosition = 0
290and _text_table_dictionary.TableName = #temp1.TableName
291
292go
293
294select
295
296'print ''----------------------------------------------''; '+char(13)+char(10)+
297'print ''Copying '+TableSchema+'.['+RTRIM(TableName)+']''; '+char(13)+char(10)+
298'truncate table MesTest_DontUseIt.'+TableSchema+'.['+RTRIM(TableName)+'];'+char(13)+char(10)+
299
300'declare @id int;'+char(13)+char(10)+
301'set @id = 0;'+char(13)+char(10)+
302'select @id = MAX(IsIdentity) from _text_table_dictionary where TableName = ''' + RTRIM(TableName) + ''';'+char(13)+char(10)+
303
304'if ( @id > 0 )'+char(13)+char(10)+
305'begin'+char(13)+char(10)+
306' set IDENTITY_INSERT MesTest_DontUseIt.'+TableSchema+'.['+RTRIM(TableName)+'] on;'+char(13)+char(10)+
307'end'+char(13)+char(10)+
308
309'insert into MesTest_DontUseIt.'+TableSchema+'.['+RTRIM(TableName)+'] ('+RTRIM(temp)+') select '+RTRIM(temp)+' from MesProdMTL.'+TableSchema+'.['+RTRIM(TableName)+']'+char(13)+char(10)+
310
311'if ( @id > 0 )'+char(13)+char(10)+
312'begin'+char(13)+char(10)+
313' set IDENTITY_INSERT MesTest_DontUseIt.'+TableSchema+'.['+RTRIM(TableName)+'] off;'+char(13)+char(10)+
314'end'+char(13)+char(10)+
315
316'go'
317from _text_table_dictionary where _text_table_dictionary.OrdinalPosition = 0 and TableName <> '_text_table_dictionary'
318
319-- select * from _text_table_dictionary
320
321*/
322/*
323
324-- -- -- -- -- -- -- -- --
325--
326-- DELETE ALL CONSTRAINT
327--
328-- -- -- -- -- -- -- -- --
329SELECT 'ALTER TABLE ' + sys.schemas.name+'.['+ sys.tables.name + '] DROP CONSTRAINT [' + sys.foreign_keys.name + ']'
330-- SELECT sys.schemas.name+'.'+ sys.tables.name AS TableName, sys.foreign_keys.name AS Expr1
331FROM sys.tables INNER JOIN
332sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id INNER JOIN
333sys.foreign_keys ON sys.tables.object_id = sys.foreign_keys.parent_object_id
334
335*/
Comments