CODEWITHSUNDEEP
sqlsql-server
300
300

Reputation: 1031

How to get data definition of all tables from SQL Server database

SQL Server 2012 and I am using SQL Server Management Studio 2008 version 10. I can get list of all tables from schema using:

SELECT TABLE_NAME 
FROM information_schema.tables;

And I can get all details about a particular table say table1 using:

sp_help table1;

Is there a way I can get all details (data definition) for all the tables in the schema in a xls? Something like by combining above two queries like (this is not valid SQL):

sp_help in (SELECT TABLE_NAME FROM information_schema.tables)

Upvotes: 1

Views: 3124

Answers (1)

BJones
BJones

Reputation: 2460

Taken basically straight from the stored proc with a little modification. Note this only shows one portion of the SP. You may have to go into the SP yourself to grab more if needed. All you need to do is right-click and choose modify.

--SP_HELP Table1
DECLARE @dbname SYSNAME
    ,@no VARCHAR(35), @yes VARCHAR(35), @none VARCHAR(35)
SELECT @no = 'no', @yes = 'yes', @none = 'none'

DECLARE @precscaletypes NVARCHAR(150)
SELECT @precscaletypes = N'TINYINT,SMALLINT,DECIMAL,INT,BIGINT,REAL,MONEY,FLOAT,NUMERIC,SMALLMONEY,DATE,TIME,DATETIME2,DATETIMEOFFSET,'




SELECT 
        'Column_name'           = name,
        'Type'                  = TYPE_NAME(user_type_id),
        'Computed'              = CASE WHEN ColumnProperty(object_id, name, 'IsComputed') = 0 THEN @no ELSE @yes END,
        'Length'                    = CONVERT(INT, max_length),
        'Prec'                  = CASE WHEN  CHARINDEX(TYPE_NAME(system_type_id) + ',', @precscaletypes) > 0
                                    THEN CONVERT(CHAR(5),ColumnProperty(object_id, name, 'precision'))
                                    ELSE '     ' END,
        'Scale'                 = CASE WHEN  CHARINDEX(TYPE_NAME(system_type_id) + ',', @precscaletypes) > 0
                                    THEN CONVERT(CHAR(5),OdbcScale(system_type_id,scale))
                                    ELSE '     ' END,
        'Nullable'              = CASE WHEN  IS_NULLABLE = 0 THEN 'no' ELSE 'yes' END,
        'TrimTrailingBlanks'    = CASE ColumnProperty(OBJECT_ID, name, 'UsesAnsiTrim')
                                    WHEN 1 THEN @no
                                    WHEN 0 THEN @yes
                                    ELSE '(n/a)' END,
        'FixedLenNullInSource'  = CASE 
                    WHEN TYPE_NAME(system_type_id) NOT IN  ('VARBINARY','VARCHAR','BINARY','CHAR')
                        THEN '(n/a)'
                    WHEN IS_NULLABLE = 0 THEN @no ELSE @yes 
                    END,
        'Collation'     = collation_name
    FROM sys.all_columns WHERE OBJECT_ID IN (SELECT OBJECT_ID(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES)

Upvotes: 1

Related Questions