How to change one attribute in a table using T-SQL to allow nulls (not null --> null)? Alter table maybe?
This question is related to
sql
sql-server
sql-server-2008
This is the approach to do this: -
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_CATALOG = 'DBName' AND
TABLE_SCHEMA = 'SchemaName' AND
TABLE_NAME = 'TableName' AND
COLUMN_NAME = 'ColumnName')
BEGIN
ALTER TABLE DBName.SchemaName.TableName ALTER COLUMN ColumnName [data type] NULL
END
If you don't have any schema then delete the schema line because you don't need to give the default schema.
For MySQL, MariaDB
ALTER TABLE [table name] MODIFY COLUMN [column name] [data type] NULL
Use MODIFY COLUMN
instead of ALTER COLUMN
.
So the simplest way is,
alter table table_name change column_name column_name int(11) NULL;
ALTER TABLE is right:
ALTER TABLE MyCustomers ALTER COLUMN CompanyName VARCHAR(20) NULL
I wrote this so I could edit all tables and columns to null at once:
select
case
when sc.max_length = '-1' and st.name in ('char','decimal','nvarchar','varchar')
then
'alter table [' + so.name + '] alter column [' + sc.name + '] ' + st.name + '(MAX) NULL'
when st.name in ('char','decimal','nvarchar','varchar')
then
'alter table [' + so.name + '] alter column [' + sc.name + '] ' + st.name + '(' + cast(sc.max_length as varchar(4)) + ') NULL'
else
'alter table [' + so.name + '] alter column [' + sc.name + '] ' + st.name + ' NULL'
end as query
from sys.columns sc
inner join sys.types st on st.system_type_id = sc.system_type_id
inner join sys.objects so on so.object_id = sc.object_id
where so.type = 'U'
and st.name <> 'timestamp'
order by st.name
ALTER TABLE public.contract_termination_requests
ALTER COLUMN management_company_id DROP NOT NULL;
Yes you can use ALTER TABLE
as follows:
ALTER TABLE [table name] ALTER COLUMN [column name] [data type] NULL
Quoting from the ALTER TABLE
documentation:
NULL
can be specified inALTER COLUMN
to force aNOT NULL
column to allow null values, except for columns in PRIMARY KEY constraints.
Source: Stackoverflow.com