• SQL server 语句获取所有数据库名、表名、字段名、表字段长度


    http://blog.csdn.net/zhengguangyi130/article/details/8062418

    获取数据库中所有的表
    SELECT SysObjects.name AS Tablename FROM sysobjects WHERE xtype = 'U'

    获取数据库中所有表的列名
    SELECT SysColumns.name AS Columnsname, SysObjects.name AS Tablename FROM SysObjects, SysColumns WHERE Sysobjects.Xtype='u' AND Sysobjects.Id=Syscolumns.Id


    获取SQL所有数据库名、所有表名、所有字段名、表字段长度
    1.获取MSSQL中的所有数据库名:
    SELECT name FROM MASter..SysDatabASes ORDER BY name


    2.获取MSSQL中的所有用户表名:
    SELECT name FROM DatabASename..SysObjects WHERE XType='U' ORDER BY name
    XType='U':表示所有用户表;
    XType='S':表示所有系统表;


    3.获取指定表[tb_phone]的所有字段名:
    SELECT name FROM SysColumns WHERE id=Object_Id('tb_phone')


    4.SQL所有表的表名、所有字段名、表字段长度
    SELECT table_name AS 数据表名,
    column_name AS 字段名,
    ISNULL(column_default,'') AS 默认值,
    is_nullable AS 是否允许为NULL,
    data_type AS 数据类型,
    ISNULL(ISNULL(ISNULL(character_maximum_length,numeric_precision),datetime_precision),1) AS 类型长度
    FROM information_schema.columns
    WHERE NOT table_name IN('sysdiagrams','dtproperties')


    5.获取指定表[tb_phone]的表名,表字段名,字段类型和类型长度
    SELECT SysObjects.name AS Tablename,
    Syscolumns.name AS Columnsname,
    Systypes.name AS DateType,
    Syscolumns.length AS DateLength
    FROM Sysproperties RIGHT OUTER JOIN
    Sysobjects INNER JOIN
    Syscolumns ON Sysobjects.id = Syscolumns.id INNER JOIN
    Systypes ON Syscolumns.xtype = Systypes.xtype ON
    Sysproperties.id = Syscolumns.id AND
    Sysproperties.smallid = Syscolumns.colid
    WHERE (Sysobjects.xtype = 'u' OR
    Sysobjects.xtype = 'v') AND (Systypes.name <> 'Sysname') AND
    (Sysobjects.name = 'tb_phone')
    ORDER BY Columnsname

  • 相关阅读:
    【刷题】BZOJ 1036 [ZJOI2008]树的统计Count
    【刷题】BZOJ 1180 [CROATIAN2009]OTOCI
    【刷题】BZOJ 1453 [Wc]Dface双面棋盘
    【刷题】BZOJ 4025 二分图
    【模考】2018.04.08 Connection
    【模考】2018.04.08 Travel
    【刷题】BZOJ 4825 [Hnoi2017]单旋
    【刷题】洛谷 P3613 睡觉困难综合征
    【刷题】BZOJ 3668 [Noi2014]起床困难综合症
    CSS3_边框 border 详解_一个 div 的阴阳图
  • 原文地址:https://www.cnblogs.com/weikai/p/3529747.html
Copyright © 2020-2023  润新知