dtWekYqd

1USE MyDB
2
3SET NOCOUNT ON
4
5CREATE TABLE ##nullable  (
6    ID INT IDENTITY(1,1),
7    SchName VARCHAR(128),
8    TblName VARCHAR(128),
9    ColName VARCHAR(128),
10    hasNulls BIT,
11    PRIMARY KEY(ID)
12    )
13
14DECLARE @currTbl VARCHAR(128)
15DECLARE @currCol VARCHAR(128)
16DECLARE @currSch VARCHAR(128)
17DECLARE @limit INT
18DECLARE @i INT
19DECLARE @sql NVARCHAR(4000)
20
21INSERT INTO ##nullable (
22    SchName,
23    TblName,
24    ColName,
25    hasNulls
26    )
27SELECT
28    c.TABLE_SCHEMA,
29    c.TABLE_NAME,
30    c.COLUMN_NAME,
31    0 AS hasNulls
32FROM INFORMATION_SCHEMA.COLUMNS c
33INNER JOIN INFORMATION_SCHEMA.TABLES t
34ON c.TABLE_CATALOG = t.TABLE_CATALOG
35AND c.TABLE_NAME = t.TABLE_NAME
36AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
37WHERE c.IS_NULLABLE = 'YES'
38AND t.TABLE_TYPE = 'BASE TABLE'
39
40SET @limit = (SELECT MAX(ID) FROM ##nullable)
41SET @i = 1
42
43WHILE @i <= @limit
44BEGIN   
45    SELECT @currSch = SchName,
46        @currTbl = TblName,
47        @currCol = ColName
48    FROM ##nullable 
49    WHERE ID = @i
50
51    SET @sql = 'UPDATE ##nullable 
52        SET hasNulls = 1
53        WHERE ID = ' + CAST(@i AS VARCHAR(20)) + ' 
54        AND EXISTS (SELECT 1 FROM ' + QUOTENAME(@currSch) + '.'+ QUOTENAME(@currTbl) + ' 
55        WHERE ' + QUOTENAME(@currCol) + ' IS NULL)'
56
57    EXEC(@sql)
58
59    SET @i = @i + 1     
60END
61
62SELECT DISTINCT * FROM ##nullable 
63WHERE hasNulls = 0  
64
65DROP TABLE ##nullable 
66     
67CREATE TABLE #Results
68(
69object_name nvarchar(500),
70column_name  nvarchar(500)
71)
72
73exec sys.sp_MSforeachtable '
74IF EXISTS(
75SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(''?'') AND is_nullable=1)
76BEGIN
77RAISERROR(''Processing ?'',0,1) WITH NOWAIT
78
79
80DECLARE @ColList nvarchar(max)
81DECLARE @CountList nvarchar(max)
82
83SELECT @ColList = ISNULL(@ColList + '','','''') + QUOTENAME(name),
84       @CountList = ISNULL(@CountList + '','','''') + ''COUNT(*) 
85          - COUNT(CASE WHEN '' + QUOTENAME(name) + '' IS NOT NULL THEN 1 END) AS '' + QUOTENAME(name)
86FROM sys.columns WHERE object_id = OBJECT_ID(''?'')  AND is_nullable=1 
87
88DECLARE @dynsql nvarchar(max)
89
90SET @dynsql = ''
91WITH T AS
92(
93SELECT 
94        ''''?'''' AS table_name, 
95        '' + @CountList + ''
96FROM ?
97)
98INSERT INTO #Results
99SELECT  table_name, col
100FROM T
101UNPIVOT (NullCount FOR col IN ('' + @ColList + '')) AS UnPvt
102WHERE NullCount = 0
103''
104
105EXEC(@dynsql)
106END
107'
108
109
110SELECT *
111FROM #Results
112
113DROP TABLE #Results 
114     
115declare @col varchar(255), @cmd varchar(max)
116
117DECLARE getinfo cursor for
118SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
119WHERE t.Name = 'ADDR_Address'
120
121OPEN getinfo
122
123FETCH NEXT FROM getinfo into @col
124
125WHILE @@FETCH_STATUS = 0
126BEGIN
127    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ADDR_Address WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
128    EXEC(@cmd)
129
130    FETCH NEXT FROM getinfo into @col
131END
132
133CLOSE getinfo
134DEALLOCATE getinfo 
135     
136use DBTest; GO;
137
138SELECT
139    c.TABLE_CATALOG as DatabaseName,
140    c.TABLE_SCHEMA as SchemaName,
141    c.TABLE_NAME as TableName,
142    c.COLUMN_NAME as ColumnName,
143    c.IS_NULLABLE as IsNullable 
144FROM INFORMATION_SCHEMA.COLUMNS c
145WHERE c.TABLE_CATALOG = 'DbTest'
146and c.TABLE_SCHEMA = 'dbo'
147AND c.TABLE_NAME = 'TableTest'
148and c.IS_NULLABLE = 'YES'