• 查询Sql Server数据库对象结构


    查询Sql Server数据库对象结构

    1. 查询数据库
    2. 查询架构
    3. 查询表
    4. 查询列
    5. 查询存储过程
    6. 查询视图

    1.查询某一服务器下所有数据库

    select t.[name] as 数据库
    from sys.databases as t
    order by name
    

    2.查询某一数据库的架构

    select name as 架构,
    	schema_id as 架构Id
    	--,principal_id
    from sys.schemas
    where principal_id = 1;
    

    3.查询某一数据库的表

    select s.name+'.'+t.name as 表,
    	t.object_id as 表Id
    from sys.tables as t
    inner join sys.schemas as s on t.schema_id = s.schema_id and s.principal_id=1 	
    

    4.查询某一数据库的所有的列

    select
    	s.name as 架构,
    	d.NAME as 表,
    	A.NAME as 列,
    	COLUMNPROPERTY( a.id,a.name,'IsIdentity') as 是否为自增, --0非自增 1自增
    	B.NAME AS 数据类型,
    	COLUMNPROPERTY(A.ID,A.NAME,'PRECISION') AS 精度,
    	ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'Scale'),0) AS 保留位,
    	A.ISNULLABLE AS 是否为空, -- 0空 1非空,
    	ISNULL(E.TEXT,'') as 默认值,
    	ISNULL(g.[value],'') AS 列说明
    FROM syscolumns a  
    LEFT JOIN systypes b ON a.xtype=b.xusertype
    INNER JOIN sys.objects d ON  a.id=d.object_id AND d.type='U'AND d.name<>'dtproperties' 
    inner join sys.schemas as s on d.schema_id=s.schema_id
    LEFT JOIN syscomments e ON a.cdefault=e.id 
    LEFT JOIN sys.extended_properties g ON a.id=g.major_id AND a.colid=g.minor_id 
    LEFT JOIN sys.extended_properties f ON d.object_id=f.major_id AND f.minor_id =0
    

    5.查询存储过程

    select 
    	s.Name as 架构,
    	p.Name as 存储过程,
    	sm.definition as 存储过程内容		 
    from sys.procedures as  p
    inner join sys.sql_modules sm ON p.object_id = sm.object_id
    inner join sys.schemas as s on p.schema_id=s.schema_id
    

    6.查询视图

    select 
    	s.name+'.'+t.name as 视图,
    	t.object_id as 视图Id
    from sys.views as t
    inner join sys.schemas as s on t.schema_id = s.schema_id and s.principal_id=1
  • 相关阅读:
    互联网产品经理入门知识
    ceph的架构和概念学习
    使用cephadm安装ceph octopus
    split命令,文件切割
    openssh升级到8.4版本
    Shell写一个显示目录结构
    nsenter 工具的使用
    『Spring Boot 2.4新特性』减少95%内存占用
    Dubbo 一篇文章就够了:从入门到实战
    for update 和 rowid 的区别
  • 原文地址:https://www.cnblogs.com/wuyunblog/p/6900762.html
Copyright © 2020-2023  润新知