Cycling Thru Trans-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cycling Thru Trans-SQL

Author

Topic

silas2
Yak Posting Veteran

65 Posts

Posted - 2005-08-31 : 07:00:24

I’m really scratching my head with this Transact-SQL, say you wanted to cycle through a set of rows, then perform an operation on each row, so in VB/DAO it might look like this:

   Dim rsTables As Recordset, rsIndex As Recordset
    Set rsTables = dbSource.OpenRecordset("SELECT * FROM INFORMATION_SCHEMA.tables")
    Do While rsTables.EOF
        Set rsIndex = dbSource.OpenRecordset("Select * From SysIndexes Where Name = '" & rsTables!Table_Name & "'")
        DoSomethingToIndex rsIndex!Name
        rsTables.MoveNext
    Loop

How can you do this cycling with Trans-SQL?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-31 : 07:14:52

What exactly are you trying to accomplish?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 07:15:06

Usually Joins will do
What do you want to do?
See this is what you wanted

Select Table_name,column_name from Information_Schema.Columns order by table_Name

Madhivanan

Failing to plan is Planning to fail

silas2
Yak Posting Veteran

65 Posts

Posted - 2005-08-31 : 07:48:39

I'm really trying to get more familiar with Trans-SQL, but specifically, I've upsized a db with 100 tables (with more in the pipline) and every table has a non-clustered index on it which I want to change to clustered (following advice from the SQL Team). I thought it would be better to cycle through the tables, their indexes and alter/drop/recreate them thru code.

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 08:33:40

I would write some SQL that generated the SQL - and then execute the resulting output.

To change all/some of your Non Clustered indexees to Clustered I would script all the indexes, do some sort of find & replace, and then run the script.

Enterprise Manager : Generate SQL will generate you a script of DROP / CREATE for all indexes. You could change all the PK ones to Clustered (maybe not all, but you could decide case-by-case) and if you wind up dropping and recreating some other indexes it won't hurt (might take a while if your database is huge, but I doubt that's an issue with the stage that I'm guessing you are at)

Kristen

silas2
Yak Posting Veteran

65 Posts

Posted - 2005-09-01 : 09:55:20

Just to clarify:
In Trans-SQL, is there a way of cycling thru the rows of a result set from a select statement like, for example, using Data Access Objects with a programming language?
e.g.


rs = GetRows("Select Statement")
For Each Row in rs.Rows
    DoSomethingWithRow (Row)
Next

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 10:02:37

You would most probably need to use a cursor.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 10:14:00

But if possible Cursors can be replaced by Set Based approach

Can you post your actual requirements by giving table structure, sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-01 : 12:53:13

Actually, if you're following Kristen's suggestion about generating the SQL, you don't need a cursor. You would simply copy and paste the generated SQL into a new Query Analyzer window and run it. There's no point in writing a VB app to do this since you only have to create the clustered indexes once.

silas2
Yak Posting Veteran

65 Posts

Posted - 2005-09-01 : 13:31:19

sorry, I don't quite get how one piece of sql can "globally" alter all the tables' constraints - without iterating through them I mean, get you give me some sort of example?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-01 : 16:05:44

Run the following in query analyzer:

SELECT 'CREATE CLUSTERED INDEX ix_CLS_' + c.table_name + ' ON ' + c.table_name + '(' + c.column_name + ')'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON C.table_name=T.table_name
WHERE T.TABLE_TYPE='BASE TABLE' AND C.ORDINAL_POSITION=1

That will generate the CREATE INDEX statements for each table, using the first column (ORDINAL_POSITION=1) as the index key. Copy that code and paste it into a new Query Analyzer window, DON'T RUN IT YET.

Naturally you may not always want to cluster on the first column, but for the ones that you do you would simply run each CREATE INDEX statement. You can highlight entire sections and run them, it's up to you. The point is, this method lets you generate any number of SQL statements to your requirements and let you run them in batches.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources