T-SQL: SQL SERVER CURSOR A Simple Example Using a Cursor

T-SQL: SQL SERVER CURSOR A Simple Example Using a Cursor

First Create Table

CREATE TABLE [dbo].[Tbl_Broadcast_Mktg](
       [Broadcast_Id] [int] IDENTITY(1,1) NOT NULL,
       [Broadcast_Campaign] [nvarchar](100) NULL,
       [Broadcast_Coupon] [int] NULL,
       [Broadcast_Custdb] [int] NULL,
       [Broadcast_Custdb_Flag] [nvarchar](50) NULL,
       [Broadcast_Date] [datetime] NULL,
       [SMS_count] [nvarchar](50) NULL,
       [total_sms_sent] [nvarchar](50) NULL,
       [total_broadcast_redemption] [nvarchar](50) NULL,
       [total_redemption_sale] [nvarchar](50) NULL,
       [total_redemption_percentage] [nvarchar](50) NULL,
       [broadcast_status_type] [nvarchar](50) NULL,
       [User_Id] [int] NULL,
       [Broadcast_Status] [int] NULL,
       [Db_type] [nvarchar](50) NULL,
       [tot_sms_count] [numeric](18, 0) NULL,
       [tot_targeted_sms_count] [numeric](18, 0) NULL,
       [tot_nontargeted_sms_count] [numeric](18, 0) NULL,
       [sms_api_code] [nvarchar](50) NULL,
       [sms_api_account] [nvarchar](50) NULL,
       [campaign_cost] [numeric](18, 3) NULL,
       [tot_sms_sent] [numeric](18, 0) NULL,
 CONSTRAINT [PK_Tbl_Broadcast_Mktg] PRIMARY KEY CLUSTERED(       [Broadcast_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 ALTER TABLE [dbo].[Tbl_Broadcast_Mktg] ADD  CONSTRAINT [DF_Tbl_Broadcast_Mktg_Broadcast_Date]  DEFAULT (getdate()) FOR [Broadcast_Date]
ALTER TABLE [dbo].[Tbl_Broadcast_Mktg] ADD  CONSTRAINT [DF_Tbl_Broadcast_Mktg_Broadcast_Status]  DEFAULT ((1)) FOR [Broadcast_Status]

 

Here We Will Alter Few Nvarchar Columns In To Numeric Data Type Using CURSOR

 DECLARE @DBName nvarchar(50), @ColumnName nvarchar(50), @DataType nvarchar(50)         
 DECLARE DataType_cursor CURSOR    FOR              SELECT   DB_Name() 'DBName',  c.name 'ColumnName',    t.Name 'DataType'  FROM sys.columns c       INNER JOIN              sys.types t ON c.user_type_id = t.user_type_id       LEFT OUTER JOIN              sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id       LEFT OUTER JOIN              sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id       WHERE
              c.object_id = OBJECT_ID('Tbl_Broadcast_MKtg')              and c.name in('tot_sms_sent','total_broadcast_redemption','total_redemption_sale','campaign_cost')
              and t.Name <> 'numeric'
             OPEN DataType_cursor
 FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType
 WHILE @@FETCH_STATUS = 0        
BEGIN                    PRINT 'DBName : '+ convert(varchar(20),@DBName)+', ColumnName : '+@ColumnName+ ', DataType : '+convert(varchar(20),@DataType)
              SELECT @DBName, @ColumnName, @DataType                                          DECLARE @SQLQuery AS NVARCHAR(500)
              /* Build Transact-SQL String with parameter value */
              SET @SQLQuery = 'alter table      Tbl_Broadcast_MKtg alter column ' + @ColumnName + ' numeric'                           /* Execute Transact-SQL String */
              EXECUTE(@SQLQuery)
        FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType                           ENDCLOSE DataType_cursor
 DEALLOCATE DataType_cursor
 

Alter columns of a table in multiple database server using sp_msforeachdb cursor dynamic query 

DECLARE @AllTables table (DBName nvarchar(4000),ColumnName nvarchar(4000),DataType nvarchar(4000))
DECLARE @SQL   nvarchar(4000)
SET @SQL=' USE [?]
IF DB_ID(''?'') > 4    BEGIN        USE [?]               IF EXISTS (select * from sys.tables where name = N''Tbl_Broadcast_MKtg'')                           BEGIN                                  DECLARE @DBName nvarchar(50), @ColumnName nvarchar(50), @DataType nvarchar(50)                                         DECLARE DataType_cursor CURSOR    FOR                           SELECT   DB_Name() ''DBName'',  c.name ''ColumnName'',    t.Name ''DataType''  FROM sys.columns c                           INNER JOIN                                  sys.types t ON c.user_type_id = t.user_type_id                           LEFT OUTER JOIN                                  sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id                           LEFT OUTER JOIN                                  sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id                           WHERE                                  c.object_id = OBJECT_ID(''Tbl_Broadcast_MKtg'')                                  and c.name in(''tot_sms_sent'',''total_broadcast_redemption'',''total_redemption_sale'',''campaign_cost'')                                  and t.Name <> ''numeric''                                                      OPEN DataType_cursor                                         FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType                      WHILE @@FETCH_STATUS = 0                                    BEGIN                                                                                               DECLARE @SQLQuery AS NVARCHAR(500)                                  /* Build Transact-SQL String with parameter value */                                  SET @SQLQuery = ''alter table     Tbl_Broadcast_MKtg alter column '' + @ColumnName + '' numeric''                                                                   /* Execute Transact-SQL String */                                  EXECUTE(@SQLQuery)                                                                   SELECT @DBName, @ColumnName, @DataType                            FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType                                                                   END                     CLOSE DataType_cursor                     DEALLOCATE DataType_cursor              END    END             'INSERT INTO @AllTables (DBName,ColumnName,DataType)
    EXEC sp_msforeachdb @SQLSET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1