如何将ms sql表模式导出到excel

Is there any way to export all table schema export to excel?

是否有办法将所有表模式导出到excel?

2 个解决方案

#1

I have taken some code from the definition of SP_HELP for reference and here is something that may help you but still you have to copy the data in excel manually :

我从SP_HELP的定义中取了一些代码作为参考，这里有一些可以帮助您的东西，但是您仍然需要手工复制excel中的数据:

select 
    'Table_name'            = ao.name,
    'Column_name'           = ac.name,
    'Type'                  = type_name(user_type_id),
    'Computed'              = case when ColumnProperty(ac.object_id, ac.name, 'IsComputed') = 0 then 'no' else 'yes' end,
    'Length'                    = convert(int, max_length),
    -- for prec/scale, only show for those types that have valid precision/scale
    -- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
    'Prec'                  = case when charindex(type_name(system_type_id) + ',',  N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,') > 0
                                then convert(char(5),ColumnProperty(ac.object_id, ac.name, 'precision'))
                                else '     ' end,
    'Scale'                 = case when charindex(type_name(system_type_id) + ',',  N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,') > 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(ac.object_id, ac.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 ac
    INNER JOIN sys.all_objects ao
        ON ac.object_id = ao.object_id and ao.type = N'U'
ORDER BY ao.object_id, ac.column_id

#2

DECLARE @OBJ_TBLNAME VARCHAR(100) = 'touchnet'
DECLARE @OUTTBL AS TABLE (NAME VARCHAR(200), DATATYPE VARCHAR(200), NULLABLE VARCHAR(10))

declare @mytbl as table(TABLE_NAME VARCHAR(500))
INSERT INTO @mytbl SELECT TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_CATALOG=@OBJ_TBLNAME GROUP BY TABLE_NAME

WHILE((SELECT COUNT(*) FROM @mytbl)>0)
BEGIN
    DECLARE @TBLNAME VARCHAR(200)
    SELECT TOP 1 @TBLNAME = TABLE_NAME FROM @mytbl GROUP BY TABLE_NAME
    INSERT INTO @OUTTBL(NAME, DATATYPE, NULLABLE) SELECT @TBLNAME, '', ''

    INSERT INTO @OUTTBL(NAME, DATATYPE, NULLABLE) SELECT 'Colum Name', 'Data Type', 'Type'
    INSERT INTO @OUTTBL
    select COLUMN_NAME AS NAME, CONVERT(varchar, DATA_TYPE) + (case when ISNULL(CHARACTER_MAXIMUM_LENGTH,-1)=-1 then '' else '('+CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH)+')' end) AS DATATYPE, (CASE when is_nullable = 'YES' then 'NOT NULL' else 'NULL' end) AS NULLABLE  from INFORMATION_SCHEMA.COLUMNS
    where TABLE_CATALOG=@OBJ_TBLNAME and TABLE_NAME=@TBLNAME 
    INSERT INTO @OUTTBL(NAME, DATATYPE, NULLABLE) SELECT '', '', ''
    DELETE TOP(1) FROM @mytbl
END

SELECT * FROM @OUTTBL