SQL Server: Tips and Tricks - 2 (2017)
Get List of All Null and Not Null Columns in SQL Server Database
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?
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
--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
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,
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