SQL Server Performance

DMV to get all column names in the database along with its table name and column length

Discussion in 'ALL SQL SERVER QUESTIONS' started by anonymous2009, Dec 8, 2011.

  1. anonymous2009 New Member

    Hello,

    I have a database xyz and need to get all the table names, its column names and its length.
    - Which DMV should I use?
    - Is there a DMV query that I can use for this?
    - I have 10 databases within the same instance.
    How to get the table names, column names, and column lengths for a particular database (eg: XYZ) alone?

    Thanks!
  2. FrankKalis Moderator

    I'm not aware of a DMV that does something similar out of the box. You might however use this as a starter:
    Code:
    DECLARE @Databases TABLE (dbname sysname);
    DECLARE @Database sysname;
    DECLARE @DatabaseCmd sysname;
    DECLARE @sql nvarchar(2000)
    
    INSERT INTO @Databases
        (dbname)
    SELECT
        D.name
    FROM
        sys.databases D
    WHERE
        D.database_id > 5;    
    
    SELECT
        @Database = MIN(D.dbname),
        @DatabaseCmd = @Database + '.sys.sp_ExecuteSQL'
    FROM
        @Databases D;
    WHILE @Database IS NOT NULL
    BEGIN
        SELECT @sql = '
        SELECT
            DB_NAME() as CurrentDatabase,
            O.name AS TableName,
            C.name AS ColumnName,
            C.column_id,
            S.name,
            S.length,
            C.precision,
            C.scale,
            C.is_nullable
        FROM
            sys.objects O
            JOIN
            sys.columns C ON O.object_id = C.object_id
            JOIN
            sys.systypes S ON C.system_type_id = S.xtype
        WHERE
            O.type = ''U'' AND
            O.is_ms_shipped = 0
        ORDER BY
            O.name,
            C.column_id'
    
        EXEC @DatabaseCmd @sql;
        
        DELETE
            @Databases
        WHERE
            dbname = @Database;
            
        SELECT
            @Database = MIN(D.dbname),
            @DatabaseCmd = @Database + '.sys.sp_ExecuteSQL'
        FROM
            @Databases D;
    END
    
  3. satya Moderator

    Such kind of information is handled through INFORMATION_SCHEMA views or user defined queries.
  4. FrankKalis Moderator

    It's easy to change my querying the internal objects for querying the INFORMATION_SCHEMA views.

Share This Page