How can I export all table names plus column names from sql?

  • julia.streatfield

    Right there with Babe

    Points: 794

    Hi, I have to list all the tables in my database, plus the column names in each, and how they all link together, for a data mapping exercise. I can get the table names out but is there an easy way to get all tables plus their column names in one fell swoop? Preferably listing the properties of each column? Hope someone out there can help, don't want to be doing c3,000 tables manually! Newbie so need it simple, too! Many thanks, Jules.

  • RTaylor2208

    SSChampion

    Points: 13188

    try This.

    SELECT o.Name as TableName,

    c.Name as ColumnName,

    t.name as DataType,

    t.length as [DataLength],

    c.collation

    FROM sysobjects o

    JOIN syscolumns c

    ON o.id = c.id

    JOIN systypes t

    ON c.xtype = t.xtype

    WHERE o.xtype = 'u'

    ORDER By o.name

    MCITP SQL 2005, MCSA SQL 2012

  • julia.streatfield

    Right there with Babe

    Points: 794

    Wow that's great thanks very much. Don't support there's an extra bit that will give a count of all rows in each table? (Pushing it I know!).

    Cheers, Jules

  • joeroshan

    SSChampion

    Points: 10377

    You can add a join to sysindexes and filter for clustered index or heap (indid in (0,1)

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • julia.streatfield

    Right there with Babe

    Points: 794

    That sounds marvellous but could you add it into the original script please? my sql knowledge is about 0.1% so didn't really understand!

    Cheers, Jules

  • joeroshan

    SSChampion

    Points: 10377

    SELECT o.Name as TableName,

    i.rowcnt Row_count,

    c.Name as ColumnName,

    t.name as DataType,

    t.length as [DataLength],

    c.collation

    FROM sysobjects o

    JOIN syscolumns c

    ON o.id = c.id

    JOIN systypes t

    ON c.xtype = t.xtype

    JOIN Sysindexes i

    ON o.id=i.id

    WHERE o.xtype = 'u'

    and i.indid in (0,1)

    ORDER By o.name

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • julia.streatfield

    Right there with Babe

    Points: 794

    Fan-blimmin-tastic thanks very very much. Saved me much time. Now all I gotta do is map away! HAGW Jules

  • deb.pabst

    Grasshopper

    Points: 18

    Does anyone know how to add schema to the query?

  • Jeff Moden

    SSC Guru

    Points: 993661

    deb.pabst - Thursday, February 7, 2019 7:54 AM

    Does anyone know how to add schema to the query?

    With the idea of teaching someone to fish, the easiest way is to use a function to do so.  Lookup the Object_Schema_Name() function and lookup sys.Objects to determine which column to apply it to.

    Also remember that this is an old post and that sysobjects and sysindexes have been deprecated.  If you look those up, you'll find out what the replacements are for those.  You might also want to have a peek at sys.columns and INFORMATION_SCHEMA.COLUMNS.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • deb.pabst

    Grasshopper

    Points: 18

    Jeff,
    Thanks so much, I kept googling and found the last option you mentioned. I also appreciate the teaching to fish sentiment as it is my favorite!
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS

  • Sue_H

    SSC Guru

    Points: 89729

    If you wanted to work on updating the query to use the current catalog views, there is a document showing which old system tables map to which current views.
    Mapping System Tables to System Views

    A few things would have to change  (such as the old tables used id and the new views usually use object_id) but rewriting wouldn't be too hard and would help understanding more about the new views.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 993661

    Heh... and Sue also teaches how to unsnarl the reel!  😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jason A. Long

    SSC-Insane

    Points: 23403

    One thing I don't see anyone addressing is DRI (declared referential integrity)... AKA Foreign Key Constraints.
    This information is crucial (at least IMHO) if mapping relationships between tables is part of the goal.
    While I 100% support the idea that teaching one to fish is better than giving one a fish, putting all of this together can be a daunting task, even for seasoned DBAs and developers.
    Sooo... With that said, I hope you use the following code as a staring point not the finish line...

    SELECT
        o.object_id,
        table_name = CONCAT(SCHEMA_NAME(o.schema_id), N'.', o.name),
        c.column_id,
        column_name = c.name,
        data_type = CONCAT(t.name, psl.precision_scale_len, ni.identity_info, ni.nullability),
        fkx.fk_constraint
    FROM
        sys.objects o WITH (NOLOCK)
        JOIN sys.columns c WITH (NOLOCK)
            ON o.object_id = c.object_id
        JOIN AlignDev.sys.types t WITH (NOLOCK)
            ON c.user_type_id = t.user_type_id
        CROSS APPLY ( VALUES (CASE
            WHEN c.user_type_id IN (34,35,36,40,48,52,56,58,59,60,61,62,98,99,104,122,127,128,129,130,189,241,256) THEN N''
            WHEN c.user_type_id IN (106,108) THEN N'(' + CONVERT(NVARCHAR(10), c.precision) + ',' + CONVERT(NVARCHAR(10), c.scale) + N')'
            WHEN c.user_type_id IN (41,42,43) THEN N'(' + CONVERT(NVARCHAR(10), c.scale) + N')'
            WHEN c.user_type_id IN (165,167,173,175) THEN N'(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CONVERT(NVARCHAR(10), c.max_length) END + N')'
            WHEN c.user_type_id IN (231,239) THEN N'(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CONVERT(NVARCHAR(10), c.max_length / 2) END + N')'
        END) ) psl (precision_scale_len)
        CROSS APPLY ( VALUES (
            CASE WHEN c.is_nullable = 1 THEN N' null' ELSE N' not null' END,
            CASE WHEN c.is_identity = 0 THEN N'' ELSE (SELECT CONCAT(N' identity(', CONVERT(INT, ic.seed_value), N',', CONVERT(INT, ic.increment_value), N')') FROM AlignDev.sys.identity_columns ic WHERE c.object_id = ic.object_id AND c.column_id = ic.column_id) END
            ) ) ni (nullability, identity_info)
        OUTER APPLY (
            SELECT
                fk_constraint = CONCAT(N'CONSTRAINT ', fk.name,
                                        N' FOREIGN KEY REFFERENCES ', SCHEMA_NAME(CONVERT(INT, OBJECTPROPERTYEX(fkc.referenced_object_id, 'SchemaId'))), N'.', OBJECT_NAME(fkc.referenced_object_id), N' (', rc.name, N')',
                                        N' {ON UPDATE: ', fk.update_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS, N'} {ON DELETE: ', fk.delete_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS, N'} {IS TRUSTED: ', CASE WHEN fk.is_not_trusted = 1 THEN N'NO' ELSE N'YES' END, N'}'
                                        )
            FROM
                sys.foreign_key_columns fkc WITH (NOLOCK)
                JOIN sys.foreign_keys fk WITH (NOLOCK)
                    ON fkc.constraint_object_id = fk.object_id
                JOIN sys.columns rc WITH (NOLOCK)
                    ON fkc.referenced_object_id = rc.object_id
                    AND fkc.referenced_column_id = rc.column_id
            WHERE
                c.object_id = fkc.parent_object_id
                AND c.column_id = fkc.parent_column_id
                AND fk.is_disabled = 0
            ) fkx
    WHERE
        o.type = 'U';

  • Sue_H

    SSC Guru

    Points: 89729

    Jeff Moden - Thursday, February 7, 2019 9:15 AM

    Heh... and Sue also teaches how to unsnarl the reel!  😀

    As long as I don't have to put bait on the hook....the only part of fishing I dislike.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply