CODEWITHSUNDEEP
sql-server-2008t-sqlstored-procedurescursor
user2646320
user2646320

Reputation: 11

Cursor is becoming an never ending loop

I want to create an SP to generate the metadata for all tables using cursors in SQL. Following is the code I have tried. but its becoming a never ending loop and same data is repeated. Thanks in advance.

--SELECT * FROM information_schema.columns 

ALTER PROCEDURE p1
AS

    SET NOCOUNT ON;

    DECLARE @id INT
        ,   @tablename VARCHAR(100)
        ,   @columnname VARCHAR(100)
        ,   @datatype VARCHAR(100)
        ,   @isnullable VARCHAR(100)

    BEGIN

        DECLARE CURSOR_1 CURSOR FOR
        SELECT  TABLE_NAME
            ,   COLUMN_NAME
            ,   DATA_TYPE
            ,   IS_NULLABLE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'Employee' -- group BY table_name  

        OPEN CURSOR_1

        FETCH NEXT FROM CURSOR_1 INTO
            @tablename,
            @columnname,
            @datatype,
            @isnullable

        WHILE @@fetch_status = 0
        BEGIN
            INSERT INTO table_schema_detail (TABLE_NAME, COLUMN_NAME, DATA_TYPE, isnullable)
            VALUES (@tablename, @columnname, @datatype, @isnullable)
        END

        FETCH NEXT FROM CURSOR_1 INTO
            @tablename,
            @columnname,
            @datatype,
            @isnullable

        CLOSE CURSOR_1
        DEALLOCATE CURSOR_1

        SET NOCOUNT OFF;

    END
GO

Upvotes: 0

Views: 166

Answers (3)

Devart
Devart

Reputation: 121922

I think get meta-data from sys.columns more preferable (in your case, cursor is not necessary):

INSERT INTO dbo.table_schema_detail 
(
      TABLE_NAME
    , COLUMN_NAME
    , DATA_TYPE
    , IS_NULLABLE
)
SELECT 
      SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , c.name
    , TYPE_NAME(c.system_type_id)
    , c.is_nullable 
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
WHERE SCHEMA_NAME(o.[schema_id]) + '.' + o.name = 'dbo.Employee'
    AND o.[type] = 'U'

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

look where you have that end statement

insert INTO table_schema_detail(table_name,column_name,data_type,isnullable) VALUES(@tablename,@columnname,@datatype,@isnullable)
end

Suspect it never gets to this line 

FETCH NEXT FROM CURSOR_1 into

And agree with other comments on is a cursor the proper approach but this is an answer to the question as stated.

Upvotes: 2

Lamak
Lamak

Reputation: 70638

I don't really understand why you need to store this information on a table, since it's already available on the system views (and if you still need to store this data, why are you using a cursor?). If, like your comment says, you need to store data from 3 tables, then you could simply do:

INSERT INTO table_schema_detail(table_name,column_name,data_type,isnullable)
SELECT table_name, column_name, data_type, is_nullable 
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE table_name IN ('Employee','OtherTable1','OtherTable2')

But again, I don't see the point. At least you could store the date when this was done:

INSERT INTO table_schema_detail(table_name,column_name,data_type,isnullable,DateInserted)
SELECT table_name, column_name, data_type, is_nullable, GETDATE()
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE table_name IN ('Employee','OtherTable1','OtherTable2')

Upvotes: 3

Related Questions