6XXc7cUk

1
2If Exists (Select * From Sys.Objects Where Object_Id = Object_Id(N'[dbo].[FindTypesAndMaxUsedIntField]') And Type In (N'P', N'PC'))
3Begin
4    Drop Procedure [dbo].FindTypesAndMaxUsedIntField
5End
6
7GO
8
9Create Procedure [dbo].FindTypesAndMaxUsedIntField  
10As
11Begin
12	
13	If Object_Id('dbo.TempColumnsAndMaxValues', 'U') Is Not Null
14	Begin
15		Drop Table dbo.TempColumnsAndMaxValues
16	End
17
18	Create Table TempColumnsAndMaxValues
19	(
20		TableName NVarChar(100), 
21		ColumnName NVarChar(100),
22		IsNullable Bit, 
23		NumRows Int,
24		NumRowsWithData Int,
25		MaxValue Int
26	)
27	
28	Declare @columnName NVarChar(100)
29	Declare @tableName NVarChar(100)
30	Declare @isNullable bit
31	
32	Declare @dbCursor Cursor 
33	
34	Set @dbCursor = Cursor For
35		Select t.Name, c.Name, c.is_nullable From Sys.Columns c
36			Inner Join Sys.Tables t On t.Object_Id = c.Object_Id
37			Inner Join Sys.Schemas s On s.Schema_Id = t.Schema_Id
38			Inner Join Sys.Types ty On ty.System_Type_Id = c.System_Type_Id
39			Where s.Name = 'dbo' And ty.Name = 'Int' and t.Name Not Like 'temp%'
40			
41	Open @dbCursor
42	
43	Fetch Next From @dbCursor Into @tableName, @columnName, @isNullable
44	While @@Fetch_Status = 0 
45	Begin
46		Declare @sql NVarChar(500)
47		Set @sql = 'Insert Into TempColumnsAndMaxValues Select ''' + @tableName + ''', ''' + @columnName + ''', ' + Convert(NVarChar(1), @isNullable) +
48						', (Select Count(*) From ' + @tableName + '), ' +
49						'(Select Count(*) From ' + @tableName + ' Where IsNull(' + @columnName + ', 0) <> 0), (Select Max (' + @columnName + ') From ' + @tableName + ')'
50		Print @sql
51						
52						
53		Exec(@sql)
54		
55		Fetch Next From @dbCursor Into @tableName, @columnName, @isNullable
56	End
57			
58	Select * From TempColumnsAndMaxValues 
59	Order By NumRows Desc
60	
61	If Object_Id('dbo.TempColumnsAndMaxValues', 'U') Is Not Null
62	Begin
63		Drop Table dbo.TempColumnsAndMaxValues
64	End
65End