[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 673
  • Last Modified:

SQL WHILE Loop??

Hi All,

I have written the procedure below based on a number of different procedures(!) What I am attempting to do is get a list of ALTER TABLE statements so the collation of each columns can be altered.

As part of the collation change each of the indexes on the tables needs dropping and recreating which is where the "usp_DropIndexes" and "usp_RecreateIndexes" stuff comes from below.

The problem is that each of the tables has n number of indexes. I made an attempt below to get the proc to output the usp_Drop/RecreateIndex only once for each table but ended up getting lost becuase of my limited knowledge on this sort of stuff!

Can anyone help me out/point me in the right direction:

Cheers,
Danny

======================================================
SET NOCOUNT ON
--CREATE TEMP TABLE TO HOLD OBJECTS INVOLVED IN REBUILD
CREATE TABLE #Process
(
  TableName VarChar(100),
  ColName VarChar(100),
  Type VarChar(100),
  Length VarChar(100),
  Nullable bit
)

--POPULATE TEMP TABLE
INSERT INTO #Process (TableName, ColName, Type, Length, Nullable)
SELECT o.name, c.name, t.name, CAST(c.prec AS nvarchar(10)), c.isnullable
FROM     sysobjects o
     INNER JOIN
          syscolumns c
          INNER JOIN
               systypes t
               ON c.xusertype = t.xusertype
          ON o.id = c.id
WHERE     c.collationid IS NOT Null
AND     o.xtype = 'U'
AND     o.name <> 'dtProperties'

--CHECK TEMP TABLE CONTENTS
--SELECT * FROM #Process

--VARIABLES FOR WORKING IN LOOP

DECLARE @IntRowCount INT
DECLARE @StrTmpTable VarChar(100)
DECLARE @StrCurTable VarChar(100)
DECLARE @StrColName VarChar(100)
DECLARE @StrType VarChar(100)
DECLARE @StrLength VarChar(100)
DECLARE @Nullable bit

SELECT TOP 1 @StrCurTable=TableName,@StrColName=ColName,@StrType=Type,@StrLength=Length,@Nullable=Nullable FROM #Process
SET @IntRowCount = @@RowCount

WHILE @IntRowCount <> 0
BEGIN
  --BUILD THE DROPINDEX LINE, ITS IN A LOOP LIKE THIS BECUASE WE WANT IT ONCE PER TABLE NOT PER COLUMN
  IF @StrCurTable <> @StrTmpTable
  BEGIN
    SELECT 'EXEC usp_DropIndexes [' + @StrCurTable + ']' + CHAR(13)
  END

  --MAKE THE MAIN ALTER TABLE COMMAND FOR EVERY COLUMN
  SELECT 'ALTER TABLE [' + @StrCurTable + '] ALTER COLUMN [' + @StrColName + '] ' + @StrType + '(' + @StrLength + ') COLLATE SQL_Latin1_General_CP1_CI_AS ' + CASE WHEN @Nullable = 0 THEN 'NOT ' ELSE '' END + 'NULL' + CHAR(13)

  --FINISH OFF WITH ANOTHER LOOP TO ADD THE RECREATE INDEX PROC
  --IF @StrCurTable <> @StrTmpTable
  --BEGIN
  --  SELECT 'EXEC usp_RecreateIndexes' + StrCurTable + CHAR(13)
  --END

  SET @StrTmpTable = @StrCurTable

END

SELECT TOP 1 @StrCurTable=TableName,@StrColName=ColName,@StrType=Type,@StrLength=Length,@Nullable=Nullable FROM #Process
SET @IntRowCount = @@RowCount

WHILE @IntRowCount <> 0
BEGIN
  --FINISH OFF WITH ANOTHER LOOP TO ADD THE RECREATE INDEX PROC
  IF @StrCurTable <> @StrTmpTable
  BEGIN
    SELECT 'EXEC usp_RecreateIndexes' + @StrCurTable + CHAR(13)
  END
  SET @StrTmpTable = @StrCurTable

END

--BIN THE TEMP TABLE
DROP TABLE #Process
0
LFMSupport
Asked:
LFMSupport
  • 3
  • 2
1 Solution
 
poayseeCommented:
Hi I would like to comment below's script(if I am not misunderstood with your problem):

As you can see below's script, you "select top 1" but not "select ...". That means you need to run for one time in the while loop. Since you only select for one record, why you put in the while loop again?

SELECT TOP 1 @StrCurTable=TableName,@StrColName=ColName,@StrType=Type,@StrLength=Length,@Nullable=Nullable FROM #Process
SET @IntRowCount = @@RowCount

WHILE @IntRowCount <> 0
BEGIN
  --BUILD THE DROPINDEX LINE, ITS IN A LOOP LIKE THIS BECUASE WE WANT IT ONCE PER TABLE NOT PER COLUMN
  IF @StrCurTable <> @StrTmpTable
  BEGIN
    SELECT 'EXEC usp_DropIndexes [' + @StrCurTable + ']' + CHAR(13)
  END

0
 
LFMSupportAuthor Commented:
So you're saying rather than use:

SELECT TOP 1 @StrCurTable=TableName,@StrColName=ColName,@StrType=Type,@StrLength=Length,@Nullable=Nullable FROM #Process

I should use:

SELECT @StrCurTable=TableName,@StrColName=ColName,@StrType=Type,@StrLength=Length,@Nullable=Nullable FROM #Process


Cheers,
danny
0
 
poayseeCommented:
Yup.

Hope this can help!!!
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LFMSupportAuthor Commented:
Thanks for the hint.

Can anyone else contribute? In particular to the part below:

 IF @StrCurTable <> @StrTmpTable
  BEGIN
    SELECT 'EXEC usp_DropIndexes [' + @StrCurTable + ']' + CHAR(13)
  END

I'm trying to SELECT that line once for each table name in the #Process table.

Danny
0
 
poayseeCommented:
may be you can addin below's code:

print @StrCurTable
print @StrTmpTable
IF @StrCurTable <> @StrTmpTable
  BEGIN
    SELECT 'EXEC usp_DropIndexes [' + @StrCurTable + ']' + CHAR(13)
  END

Then you can see clearly your mistake.
0
 
DanRollinsCommented:
You can also use a cursor to cycle through the table names.    Although it might be less efficient, it would be conceptually quite simple (and bulletproof) to use code like the following in the two places where you need to loop through only the table names:

      CREATE TABLE #TblNames (
            sTableName VarChar(100),
      )
      INSERT INTO #TblNames (sTableName)
            SELECT o.name FROM sysobjects o WHERE o.xtype = 'U'
      
      DECLARE crsr CURSOR FOR SELECT * FROM #TblNames
      DECLARE @sTableName varchar(100)
      OPEN crsr
      FETCH NEXT FROM crsr INTO @sTableName
      WHILE (@@FETCH_STATUS = 0) BEGIN
            -- here, take action for each table; eg, print its name :)
            print @sTableName
            FETCH NEXT FROM crsr into @sTableName
      END
      CLOSE crsr
      DEALLOCATE crsr
      
      DROP TABLE #TblNames

-- Dan
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now