读取表、视图及其字段名称、类型

SqlServer

--读取视图
select * from sysobjects where xtype='v'

--读取表
select * from sysobjects where xtype='u'

--读取表、视图的字段名称、类型
CREATE PROCEDURE [dbo].[SysTbStructureView]
(   @TbName NVARCHAR(50))
AS
    SELECT a.name AS ColumnName,
        c.name AS TypeName,
        CASE
            WHEN a.is_nullable = 0 THEN
                'Not Null'
            ELSE
                'Null'
        END AS NullAble
    FROM sys.columns a,
        sys.objects b,
        sys.types c
    WHERE a.object_id = b.object_id
          AND b.name = @TbName
          AND a.system_type_id = c.system_type_id
          AND c.name <> 'sysname'
    ORDER BY a.column_id;
    RETURN;

MySQL

--读取视图
select TABLE_NAME as Name,TABLE_COMMENT as Description from information_schema.tables
where  TABLE_SCHEMA=(select database()) AND TABLE_TYPE='VIEW'
                                                 
--读取表                                                 
select TABLE_NAME as Name,TABLE_COMMENT as Description from information_schema.tables
where  TABLE_SCHEMA=(select database())  AND TABLE_TYPE='BASE TABLE'

--读取表、视图的字段名称、类型
SELECT
    column_name AS DbColumnName,
CASE
        
        WHEN LEFT ( COLUMN_TYPE, LOCATE( '(', COLUMN_TYPE ) - 1 ) = '' THEN
        COLUMN_TYPE ELSE LEFT ( COLUMN_TYPE, LOCATE( '(', COLUMN_TYPE ) - 1 ) 
    END AS DataType,
    CAST(
        SUBSTRING(
            COLUMN_TYPE,
            LOCATE( '(', COLUMN_TYPE ) + 1,
            LOCATE( ')', COLUMN_TYPE ) - LOCATE( '(', COLUMN_TYPE ) - 1 
        ) AS signed 
    ) AS Length,
    column_default AS `DefaultValue`,
    column_comment AS `ColumnDescription`,
CASE        
        WHEN COLUMN_KEY = 'PRI' THEN
    TRUE ELSE FALSE 
    END AS `IsPrimaryKey`,
CASE        
        WHEN is_nullable = 'YES' THEN
    TRUE ELSE FALSE 
    END AS `IsNullable` 
FROM
    Information_schema.COLUMNS 
WHERE
    TABLE_NAME = 'v_bashouse' 
    AND TABLE_SCHEMA = ( SELECT DATABASE ( ) ) 
ORDER BY
    TABLE_NAME