SQL Server: Tips and Tricks - 2 (2017)

Get List of All Null and Not Null Columns in SQL Server Database

Scenario:

You are working as SQL Server Developer, You are doing some analysis and want to get the list of all columns in SQL Server Database which are Nullable or Not Nullable. How would you get this information?

Solution:

We can use system views in SQL Server to get this information. Below Query will return us Database Name, Schema Name, Table Name, Column Name, Data Type and IS_Nullable.

SELECT Table_CataLog AS DBName

,table_Name

,Column_Name

,Data_Type

,IS_NULLABLE

FROM INFORMATION_SCHEMA.COLUMNS

--Uncomment the Where Clause if you want to filter the records

--where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'TableName' and COLUMN_NAME = 'ColumnName'

How to Alter column from Not Null to Null in SQL Server Table

Scenario:

You are working as SQL Server developer for Auto Insurance Company, You were asked to create a table with below definition where Middle Name has to be provided always. So you have created the Column with Not Null property. After some time, your company encountered some data, where customer had no Middle Name. They would like you to change the Column to accept Null if not provided. What scripts you will provide?

Create Table Customer

(FirstName VARCHAR(50) Not Null,

MiddleName VARCHAR(50) Not Null,

LastName VARCHAR(50),

Age SmallInt,

PhoneNumber CHAR(9),

DOB Date,

Gender CHAR(1)

)

Solution:

You can simply Alter the column by using below statement.

Alter table dbo.TableName

Alter Column ColumnName DataType Size Null

To Alter Middle Name to accept Null, we can use below statement.

Alter table dbo.Customer

Alter Column MiddleName VARCHAR(50) Null