cursor within a cursor - 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

SQL Server 2000 Forums

SQL Server Development (2000)

cursor within a cursor

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-28 : 08:02:04

warren writes "Hi,

I have recently started worked as a Junior SQL Server developer.

My first task it to create a database catalogue to show all the databases that my team has and all the tables within them, the fields within the tables and the constraints on the fields, etc.

I have created the following script that will populate the field table by skipping through all the tables that I have put in my tblTable:

--truncate table tblTable
--Truncate table tblfield

DECLARE @SQLcmd VarChar (1000)
DECLARE @DbName VarChar (50)
DECLARE @DbID VarChar (50)
DECLARE @TblName VarChar (50)
DECLARE @TblID VarChar (50)

DECLARE c1 Cursor For

SELECT TableName, TableID, DbName
FROM tblTable T, tblDatabase D
WHERE T.DatabaseID = D.DbID
ORDER By DatabaseID

OPEN c1

FETCH NEXT From c1
INTO @TblName, @TblID, @DbName
WHILE @@Fetch_Status = 0

BEGIN
--PRINT @DbName
SELECT @SQLcmd = '
INSERT INTO tblField
(FieldName, TableID)
SELECT COLUMN_NAME, '''+@TblID+'''
FROM '+@DbName+'.INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name = '''+@tblName+''''

PRINT @SQLcmd

EXEC (@SQLcmd)

FETCH NEXT From c1
INTO @TblName, @TblID, @DbName

END

CLOSE c1

DEAlLOCATE c1

However, I want to change this script so that when it adds a Field it then adds the constraints to the constraint table before moving on to the next field it needs to add.

I have done the following script that doestn work( i think i need to add another cursor within this cursor, but dont know how):

--truncate table tblTable
--Truncate table tblfield

DECLARE @SQLcmd VarChar (1000)
DECLARE @DbName VarChar (50)
DECLARE @DbID VarChar (50)
DECLARE @TblName VarChar (50)
DECLARE @TblID VarChar (50)
DECLARE @FieldID VarChar(50)
DECLARE @ColumnName VarChar(50)

DECLARE c1 Cursor For

SELECT TableName, TableID, DbName
FROM tblTable T, tblDatabase D
WHERE T.DatabaseID = D.DbID
ORDER By DatabaseID

OPEN c1

FETCH NEXT From c1
INTO @TblName, @TblID, @DbName
WHILE @@Fetch_Status = 0

BEGIN
--PRINT @DbName
SELECT @SQLcmd = '
INSERT INTO tblField
(FieldName, TableID)
SELECT COLUMN_NAME, '''+@TblID+'''
FROM '+@DbName+'.INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name = '''+@tblName+''''

PRINT @SQLcmd

EXEC (@SQLcmd)

SELECT @SQLcmd = '
select '''+@ColumnName+''' = COLUMN_NAME
FROM '+@DbName+'.INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name = '''+@tblName+''''

PRINT @SQLcmd

EXEC (@SQLcmd)

SELECT @SQLcmd = '
INSERT INTO tblConstraint
(ConstraintName, ConstraintType, FieldID)
SELECT C.CONSTRAINT_NAME, T.CONSTRAINT_TYPE, '''+@FieldID+'''
FROM '+@DbName+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
INNER JOIN '+@DbName+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
ON C.Constraint_Name = T.Constraint_Name
WHERE C.Column_Name = '''+@ColumnName+'''
AND C.TABLE_NAME = '''+@tblName+''''

EXEC(@SQLcmd)

FETCH NEXT From c1
INTO @TblName, @TblID, @DbName

END

CLOSE c1

DEAlLOCATE c1

Would really appreciate any help you can give me. "

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources