SQL Soundings: Find and Fix NOT NULL Columns Without DEFAULT Constraints

Tuesday, February 16, 2010

Find and Fix NOT NULL Columns Without DEFAULT Constraints

The latest and greatest version of my FindAllNotNullColumnsWithNoDefaultConstraint.sql script (formerly, embarrassingly, FindAllNonNullableColumnsWithNoDefaultConstraint.sql) is now available. It returns the DML code to add DEFAULT constraints to all NOT NULL columns that don't already have them. Useful on legacy databases to head off problems before they happen.

This is a work in progress, so the usual caveats apply: do not run this on any database you want to keep, for example, a production database, or any database that isn't backed up. Comments welcome!

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

1204 (1)

add constraint (2)

agent ransack (1)

agent_datetime (1)

aggregates (1)

all_indexes (1)

alter column (1)

alter database (1)

alter index (1)

alter table (4)

alter user (1)

ansi_nulls (2)

ansi_padding (1)

ansi_warnings (1)

arithabort (1)

autogrowth (1)

autoshrink (1)

avg (1)

b-tree (1)

backup (2)

backup_finish_date (1)

backupset (1)

batteries (1)

begin transaction (1)

between (1)

bigint (1)

bit (1)

cardinality (1)

case (3)

cast (4)

catch (1)

Central Management Servers (7)

charindex (2)

checkpoint (1)

clustered index (5)

coalesce (1)

collation (1)

comma-separated (1)

commit (1)

concat_null_yields_null (1)

convert (4)

cpu (1)

create table (1)

create user (1)

database trigger (1)

databaseproperty (1)

datalength (2)

datetime (1)

db_name (1)

dbcc checkdb (2)

dbcc cleantable (2)

dbcc dbreindex (1)

dbcc loginfo (1)

dbcc showcontig (1)

dbcc shrinkfile (1)

dbcc updateusage (2)

default constraint (3)

defragmentation (1)

delete (1)

disable trigger (1)

disk (1)

drop (2)

drop constraint (1)

drop index (1)

drop statistics (2)

drop table (1)

editionid (1)

email (1)

email_address (1)

eml files (1)

enable trigger (1)

error (1)

error_message (1)

errorlog (2)

escape (2)

exception handling (1)

exec (1)

execute as caller (1)

extended properties (1)

extent (1)

extent changes (1)

external fragmentation (2)

fillfactor (1)

floor (2)

fn_trace_gettable (1)

foreign key (1)

forwarding pointer (1)

fragmentation (2)

from (1)

function (1)

grants (1)

group by (2)

group by rollup (1)

grouping (1)

hexadecimal (1)

hobt (1)

identity (1)

implicit conversion (1)

index (6)

indexed views (1)

indexid (1)

indexproperty (1)

information_schema (1)

insert (1)

instant file initialization (1)

int (1)

internal fragmentation (1)

is_auto_shrink_on (1)

is_computed (1)

is_identity (4)

is_ms_shipped (6)

is_nullable (3)

is_primary_key (3)

is_system_named (1)

is_unique_constraint (1)

ishypothetical (1)

isnull (2)

IsReadOnly (1)

isstatistics (1)

isunique (1)

Job (1)

join (1)

ldf (1)

like (1)

linked server (1)

lock (1)

log file (1)

log10 (1)

logical fragmentation (2)

master database (4)

mdf (1)

member_principal_id (1)

money (2)

msg 1750 (1)

msg 1770 (1)

no_infomsgs (1)

nocount (2)

nomenclature (1)

non-clustered index (1)

nonclustered index (1)

nowait (1)

ntfscontrolfile (1)

nullif (1)

nvarchar (2)

nvarchar(max) (1)

object_id (2)

object_name (3)

objectproperty (1)

openquery (1)

operators (2)

order by (1)

order of evaluation (1)

outer join (1)

page splits (1)

partition (1)

permissions (1)

pivot (1)

plural (1)

primary key (2)

principals (1)

profiler (2)

quoted_identifier (2)

quotename (3)

raiserror (1)

rebuild (1)

rebuild. reorganize (1)

recovery model (1)

replace (4)

replacechar (1)

reverse (1)

role_principal_id (1)

roles (1)

rowcount (2)

rtrim (1)

scan density (1)

schema_id (2)

screen saver (2)

selectivity (1)

serverproperty (4)

sid (1)

single quotes (1)

singular (1)

skibear (1)

sp_add_notification (1)

sp_addrolemember (1)

sp_change_users_login (1)

sp_delete_notification (1)

sp_delete_operator (1)

sp_msforeachdb (11)

sp_msforeachtable (2)

sp_readerrorlog (1)

sp_send_dbmail (1)

sp_sqlexec (1)

sp_update_job (1)

sparse (1)

sql_oj_capabilities (1)

sqlcmd (1)

SQLServerPedia Syndication (55)

SSIS (3)

SSMS (5)

statistics (3)

statistics io (1)

stats_date (1)

stuff (1)

substring (2)

suser_sid (1)

sys.allocation_units (1)

sys.columns (7)

sys.data_spaces (1)

sys.database_files (2)

sys.database_role_members (1)

sys.databases (3)

sys.dm_db_index_physical_stats (1)

sys.dm_db_index_usage_stats (2)

sys.dm_db_partition_stats (2)

sys.dm_exec_cached_plans (1)

sys.dm_exec_query_plan (1)

sys.dm_exec_query_stats (1)

sys.dm_exec_requests (2)

sys.dm_exec_sql_text (1)

sys.dm_os_sys_info (1)

sys.extended_properties (2)

sys.index_columns (2)

sys.indexes (9)

sys.key_constraints (2)

sys.master_files (2)

sys.objects (6)

sys.partitions (1)

sys.schemas (1)

sys.sp_addextendedproperty (2)

sys.sp_autostats (1)

sys.sp_createstats (1)

sys.sp_dropextendedproperty (1)

sys.sp_msforeachdb (2)

sys.sp_msforeachtable (1)

sys.sql_modules (1)

sys.stats (2)

sys.stats_columns (1)

sys.sysprocesses (1)

sys.tables (11)

sys.types (3)

sysalerts (1)

syscolumns (1)

syscomments (1)

sysdatabases (3)

sysindexes (3)

sysjobhistory (2)

sysjobs (6)

sysjobs_view (1)

sysjobshistory (1)

syslogins (1)

sysname (2)

sysnotifications (1)

sysobjects (4)

sysoperators (2)

system stored procedure (1)

table (1)

tableresults (1)

tempdb (1)

templates (2)

then (1)

tinyint (1)

tinyurl.com (1)

trace (1)

trancount (1)

transactions (1)

trc (1)

trigger (1)

truncate table (1)

truncateonly (1)

try (1)

type_desc (1)

uid (1)

undocumented (1)

unique constraint (1)

use (1)

user_name (2)

varbinary (1)

views (1)

virtual log file (1)

Visio (1)

vista (1)

vista mail (1)

Visual Studio (1)

when (1)

whitespace (1)

xact_abort (2)

xp_cmdshell (4)

xp_fileexist (2)

xp_fixeddrives (1)

xp_instance_regwrite (1)

xtype (1)