Mega Code Archive

 

Categories / MSSQL Tutorial / Cursor

 

FAST_FORWARD cursor

14> 15> SELECT TABLE_NAME AS TableName, COLUMN_NAME AS ColumnName, DATA_TYPE AS Type 16> INTO #TableSummary 17> FROM INFORMATION_SCHEMA.COLUMNS 18> WHERE TABLE_NAME IN 19>      (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 20>       WHERE (TABLE_TYPE = 'BASE TABLE' AND 21>              TABLE_NAME NOT IN ('dtproperties', 'TableSummary'))) 22> 23> DECLARE @TableNameVar varchar(128), @ExecVar varchar(1000) 24> DECLARE TableSummary_Cursor CURSOR 25> FAST_FORWARD 26> FOR 27>     SELECT DISTINCT TableName 28>     FROM #TableSummary 29> 30> OPEN TableSummary_Cursor 31> FETCH NEXT FROM TableSummary_Cursor INTO @TableNameVar 32> WHILE @@FETCH_STATUS = 0 33> BEGIN 34>     SET @ExecVar = 'DECLARE @CountVar int ' + 'SELECT @CountVar = COUNT(*) ' 35>     SET @ExecVar = @ExecVar + 'FROM ' + @TableNameVar + ' ' 36>     SET @ExecVar = @ExecVar + 'INSERT #TableSummary ' 37>     SET @ExecVar = @ExecVar + 'VALUES (''' + @TableNameVar + ''',' 38>     SET @ExecVar = @ExecVar + '''*Row Count*'',' + ' @CountVar)' 39>     EXEC (@ExecVar) 40>     FETCH NEXT FROM TableSummary_Cursor INTO @TableNameVar 41> END 42> CLOSE TableSummary_Cursor 43> DEALLOCATE TableSummary_Cursor 44> GO (89 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT * FROM #TableSummary 3> ORDER BY TableName, ColumnName 4> GO TableName                                                                                                                        ColumnName                                                           Type -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------- --------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- Customers                                                                                                                        *Row Count*                                                           9 Customers                                                                                                                        Address                                                           nvarchar Customers                                                                                                                        City                                                           nvarchar Customers                                                                                                                        CompanyName                                                           nvarchar Customers                                                                                                                        ContactName                                                           nvarchar Customers                                                                                                                        ContactTitle                                                           nvarchar Customers                                                                                                                        Country                                                           nvarchar Customers                                                                                                                        CustomerID                                                           nchar Customers                                                                                                                        Fax                                                           nvarchar Customers                                                                                                                        Phone                                                           nvarchar Customers                                                                                                                        PostalCode                                                           nvarchar Customers                                                                                                                        Region                                                           nvarchar BillingCopy                                                                                                                      *Row Count*                                                           0 BillingCopy                                                                                                                      CreditTotal                                                           int BillingCopy                                                                                                                      BillingDate                                                           datetime BillingCopy                                                                                                                      BillingDueDate                                                           datetime BillingCopy                                                                                                                      BillingNumber                                                           int BillingCopy                                                                                                                      BillingTotal                                                           int BillingCopy                                                                                                                      PaymentTotal                                                           int BillingCopy                                                                                                                      TermsID                                                           int BillingCopy                                                                                                                      BankerID                                                           int MSreplication_options                                                                                                            *Row Count*                                                           3 MSreplication_options                                                                                                            install_failures                                                           int MSreplication_options                                                                                                            major_version                                                           int MSreplication_options                                                                                                            minor_version                                                           int MSreplication_options                                                                                                            optname                                                           nvarchar MSreplication_options                                                                                                            revision                                                           int MSreplication_options                                                                                                            value                                                           bit OldBillings                                                                                                                      *Row Count*                                                           0 OldBillings                                                                                                                      CreditTotal                                                           int OldBillings                                                                                                                      BillingDate                                                           datetime OldBillings                                                                                                                      BillingDueDate                                                           datetime OldBillings                                                                                                                      BillingNumber                                                           int OldBillings                                                                                                                      BillingTotal                                                           int OldBillings                                                                                                                      PaymentTotal                                                           int OldBillings                                                                                                                      TermsID                                                           int OldBillings                                                                                                                      BankerID                                                           int department_pivot                                                                                                               *Row Count*                                                           11 department_pivot                                                                                                               budget                                                           float department_pivot                                                                                                               date_month                                                           datetime department_pivot                                                                                                               dept_name                                                           char department_pivot                                                                                                               emp_cnt                                                           int department_pivot                                                                                                               month                                                           int department_pivot                                                                                                               year                                                           int SalesMw                                                                                                                          *Row Count*                                                           13 SalesMw                                                                                                                          CD_ID                                                           int SalesMw                                                                                                                          QtySold                                                           int SalesMw                                                                                                                          SalesDate                                                           datetime SalesMw                                                                                                                          StoreID                                                           int spt_fallback_db                                                                                                                  *Row Count*                                                           0 spt_fallback_db                                                                                                                  dbid                                                           smallint spt_fallback_db                                                                                                                  name                                                           varchar spt_fallback_db                                                                                                                  status                                                           smallint spt_fallback_db                                                                                                                  version                                                           smallint spt_fallback_db                                                                                                                  xdttm_ins                                                           datetime spt_fallback_db                                                                                                                  xdttm_last_ins_upd                                                           datetime spt_fallback_db                                                                                                                  xfallback_dbid                                                           smallint spt_fallback_db                                                                                                                  xserver_name                                                           varchar spt_fallback_dev                                                                                                                 *Row Count*                                                           0 spt_fallback_dev                                                                                                                 high                                                           int spt_fallback_dev                                                                                                                 low                                                           int spt_fallback_dev                                                                                                                 name                                                           varchar spt_fallback_dev                                                                                                                 phyname                                                           varchar spt_fallback_dev                                                                                                                 status                                                           smallint spt_fallback_dev                                                                                                                 xdttm_ins                                                           datetime spt_fallback_dev                                                                                                                 xdttm_last_ins_upd                                                           datetime spt_fallback_dev                                                                                                                 xfallback_drive                                                           char spt_fallback_dev                                                                                                                 xfallback_low                                                           int spt_fallback_dev                                                                                                                 xserver_name                                                           varchar spt_fallback_usg                                                                                                                 *Row Count*                                                           0 spt_fallback_usg                                                                                                                 dbid                                                           smallint spt_fallback_usg                                                                                                                 lstart                                                           int spt_fallback_usg                                                                                                                 segmap                                                           int spt_fallback_usg                                                                                                                 sizepg                                                           int spt_fallback_usg                                                                                                                 vstart                                                           int spt_fallback_usg                                                                                                                 xdttm_ins                                                           datetime spt_fallback_usg                                                                                                                 xdttm_last_ins_upd                                                           datetime spt_fallback_usg                                                                                                                 xfallback_vstart                                                           int spt_fallback_usg                                                                                                                 xserver_name                                                           varchar spt_monitor                                                                                                                      *Row Count*                                                           1 spt_monitor                                                                                                                      connections                                                           int spt_monitor                                                                                                                      cpu_busy                                                           int spt_monitor                                                                                                                      idle                                                           int spt_monitor                                                                                                                      io_busy                                                           int spt_monitor                                                                                                                      lastrun                                                           datetime spt_monitor                                                                                                                      pack_errors                                                           int spt_monitor                                                                                                                      pack_received                                                           int spt_monitor                                                                                                                      pack_sent                                                           int spt_monitor                                                                                                                      total_errors                                                           int spt_monitor                                                                                                                      total_read                                                           int spt_monitor                                                                                                                      total_write                                                           int spt_values                                                                                                                       *Row Count*                                                           2346 spt_values                                                                                                                       high                                                           int spt_values                                                                                                                       low                                                           int spt_values                                                                                                                       name                                                           nvarchar spt_values                                                                                                                       number                                                           int spt_values                                                                                                                       status                                                           int spt_values                                                                                                                       type                                                           nchar BankerBalances                                                                                                                   *Row Count*                                                           10 BankerBalances                                                                                                                   SumOfBillings                                                           int BankerBalances                                                                                                                   BankerID                                                           int (101 rows affected) 1> DROP TABLE #TableSummary 2> GO