Tables and rows listing

/*
This is my first attempt to create a script which show all the columns of a selected DB in a new table.
WARNING WARNING WARNING If a table with the same name exist, it will be overwritten
*/USE Elma /* Change ME with the database name you want */GO

/* If the table 'T_STRUTTURA' exist il will be deleted */IF (ISNULL((SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T_STRUTTURA'), '')) <> ''
    BEGIN
        DROP TABLE T_STRUTTURA
    END

/* 
Create the table T_STRUTTURA. Note that it is not a temporary table, if you want to swittch it to temporary add # to the name in all the script recurrence
*/CREATE TABLE T_STRUTTURA
    (NOME_TBL VARCHAR (255) CONSTRAINT U_T_STRUTTURA UNIQUE NONCLUSTERED (NOME_TBL, NOME_FLD) NOT NULL,
     NOME_FLD VARCHAR (30) NULL,
     FLD_TIPO VARCHAR (30) NULL,
     FLD_LMAX VARCHAR (30) NULL)

/*
Some variables
*/DECLARE
    @NOME_TABELLA VARCHAR(30),-- table name
    @NOME_COLONNA VARCHAR(30),-- column name
    @COLONNA_TIPO VARCHAR(30),-- column type
    @COLONNA_LMAX VARCHAR(30)-- column lenght

/*
I know that the cursors are not the best for someone, but I like them :) 
*/
/*
This is the cursor which contains the table names listing
*/DECLARE cur_tabella CURSOR
FOR
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_TYPE = 'BASE TABLE')
    ORDER BY TABLE_NAME

OPEN cur_tabella
    FETCH NEXT FROM cur_tabella
    INTO @NOME_TABELLA

    WHILE @@FETCH_STATUS = 0
    BEGIN
/* This is the cursor which contains the column names listing */        DECLARE cur_colonna CURSOR
        FOR
            SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @NOME_TABELLA
            ORDER BY COLUMN_NAME
            /* This start a new table, the other fields are empty so I can filter them to have a list of the table names only */            INSERT INTO T_STRUTTURA SELECT (@NOME_TABELLA + '________________________________________') AS NOME_TBL, ' ' AS NOME_FLD, ' ' AS FLD_TIPO, ' ' AS FLD_LMAX

        OPEN cur_colonna
            FETCH NEXT FROM cur_colonna
            INTO @NOME_COLONNA,
                 @COLONNA_TIPO,
                 @COLONNA_LMAX

            WHILE @@FETCH_STATUS=0
            BEGIN
 /* The main INSERT for all the columns data */                 INSERT INTO T_STRUTTURA SELECT @NOME_TABELLA AS NOME_TBL, @NOME_COLONNA AS NOME_FLD, @COLONNA_TIPO AS FLD_TIPO, @COLONNA_LMAX AS FLD_LMAX
            FETCH NEXT FROM cur_colonna
            INTO @NOME_COLONNA,
                 @COLONNA_TIPO,
                 @COLONNA_LMAX
            END
        CLOSE cur_colonna
        DEALLOCATE cur_colonna
    FETCH NEXT FROM cur_tabella
    INTO @NOME_TABELLA
    END
CLOSE cur_tabella
DEALLOCATE cur_tabella
GO


/* Some samples of how I use this table */SELECT * FROM T_STRUTTURA WHERE NOME_FLD LIKE '%codciva%'
GO

SELECT * FROM T_STRUTTURA

/* When the table is no more necessay */DROP TABLE T_STRUTTURA