Skip to main content

Posts

Showing posts from November, 2017

SQL Get all Index create script from Database

To get all script from database as a create new index into another database you can use the following --Get all Index Script SELECT ' CREATE ' + CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + I.name + ' ON ' + Schema_name(T.Schema_id)+'.'+T.name + ' ( ' + KeyColumns + ' ) ' + ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') + ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' + CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' + 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' + -- default value 'SORT_IN_TEMPDB = OFF ' + ',' + CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE

SQL Drop index script from Database

If yo want to get all drop index script from database you can use this. like '%idx_%'   = index prefix --Drop All Index declare @qry nvarchar(max); select @qry = (SELECT 'DROP INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ID) + '; ' FROM sysindexes ix WHERE ix.Name IS NOT null and ix.Name like '%idx_%' for xml path('')); SELECT @qry This will return all drop index script on result. now copy the script for your use.

SQL get all table column which is null in database

create table #SuspectColumns ( TABLE_SCHEMA sysname, TABLE_NAME sysname, COLUMN_NAME sysname ) declare csrColumns cursor fast_forward for select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE = 'YES' declare @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAME sysname, @sql nvarchar(max) open csrColumns while (1=1) begin fetch next from csrColumns into @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME if @@FETCH_STATUS<>0 break set @sql = N'if not exists(select 1 from ' + QUOTENAME(@TABLE_SCHEMA) + N'.' + QUOTENAME(@TABLE_NAME) + N' where ' + QUOTENAME(@COLUMN_NAME) + N'is not null) insert into #SuspectColumns values (''' + @TABLE_SCHEMA + N''',''' + @TABLE_NAME + N''',''' + @COLUMN_NAME + N''')' exec sp_executes