您当前的位置: AG亚游集团 > 数据库教程 > MSSQL教程 > SqlServer常用对象查询

SqlServer常用对象查询

作者:xiaoxiao 来源:未知 发布时间: 2014-07-30 11:46 点击: 次
-- 查询用户表对象信息 select Tab . Name as [ 表名 ] , Tab . create_date as [ 创建时间 ] , Tab . modify_date as [ 最后修改时间 ] , Col . Name as [ 列名 ] , Type . name as [ 数据类型 ] , Col . max_length as [ 字段长度 ] , CASE WHEN pk . is_primary_key

AG亚游集团,从规划蓝图上可以看到,未来的桃浦科技智慧城将成为上海西北部的一部巨擘,借力于真如副中心的辐射效应,无缝对接大真如板块与桃浦地区发展,推动桃浦与真如板块的联合发展,从而支撑起普陀“一轴两翼”功能布局,并引领整个普陀的形态功能联动提升。

  2006年4月5日爱立信在瑞典宣布,公司对世界首例可直接多个频道电视节目的3G电话电视技术进行了测试,并计划在不久的将来把采用这一技术的新产品投放。这项被称为MBMS的技术将是未来在移动网络上发展移动电视广播业务的技术基础。简单地说,这一技术可将同一个电视信号传送给多家接收用户,而且只占用一个通信频段,不会给有限的网络资源带来负担。这也是业界首次多媒体广播多播业务(MBMS)的现场应用演示。相比其他移动电视技术标准,爱立信的MBMS技术只要对现有3G网络进行,不用建设新的基础设施。爱立信已在实验室成功地完成了对3G手机接收电视信号技术的测试。到今年秋天,将进行第一阶段的商用推广。笨蛋也能年赚100万此外,艾利拥有众多粉丝,而她的男朋友肖恩则是她的铁杆粉丝。肖恩称,“艾利的身材深深地吸引了我,但她的内在更迷人。”艾利也非常喜欢自己的身材,但她表示也有很多苦恼,比如她需要买16号的鞋子(英国女人的鞋子通常是14号或13.5号)和超长的裤子。她希望更多像她一样的人敢于接纳自我,活出自我。(实习编译:魏慧 审稿:朱盈库)

SqlServer常用对象查询

  --查询用户表对象信息

  select Tab.Name as [表名],Tab.create_date as [创建时间],Tab.modify_date as [最后修改时间],

  Col.Name as [列名] ,Type.name as [数据类型],Col.max_length as [字段长度],

  CASE WHEN pk.is_primary_key= 1 THEN 'Y' ELSE 'N' end as [是否主键],

  CASE WHEN Col.is_identity = 1 THEN 'Y' else 'N'end as [是否自增] ,

  identity_columns.seed_value as [自增种子],identity_columns.increment_value as [自增步长],

  case when Col.is_nullable = 1 then 'Y' else 'N' END AS [是否允许为NULL],

  Def.text as [默认值],case when Col.is_computed = 1 then 'Y' else 'N' END as [是否计算列] ,

  computed_columns.definition as [计算公式],Col_Desc.Value as [列备注]

  from sys.objects Tab inner join sys.columns Col on Tab.object_id =Col.object_id

  inner join sys.types Type on Col.system_type_id = Type.system_type_id

  left join sys.identity_columns identity_columns on Tab.object_id = identity_columns.object_id and Col.column_id = identity_columns.column_id

  left join syscomments Def on Col.default_object_id = Def.ID

  left join(

  select index_columns.object_id,index_columns.column_id,indexes.is_primary_key

  from sys.indexes indexes inner join sys.index_columns index_columns

  on indexes.object_id = index_columns.object_id and indexes.index_id = index_columns.index_id

  where indexes.is_primary_key = 1/*主键*/

  ) PK on Tab.object_id = PK.object_id AND Col.column_id = PK.column_id

  left join sys.computed_columns computed_columns on Tab.object_id =computed_columns.object_id and Col.column_id = computed_columns.column_id

  left join sys.extended_properties Col_Desc on Col_Desc.major_id=Tab.object_id and Col_Desc.minor_id=Col.Column_id and Col_Desc.class=1

  where Tab.type = 'U' and Tab.Name not like'sys%'

  order by Tab.create_date

  --查询所有视图

  select views.Name as [视图名],Col.Name as [列名] ,Type.name as [数据类型],Col.max_length as [字段长度]

  --,Col_Desc.Value as Col_Description

  from sys.views views

  inner join sys.columns Col on views.object_id = Col.object_id

  inner join sys.types Type on Col.system_type_id = Type.system_type_id

  --left join sys.extended_properties Col_Desc

  -- on Col_Desc.major_id=views.object_id and Col_Desc.minor_id=Col.Column_id and Col_Desc.class=1

  order by Create_Date

  --查询外键约束

  select FK_Name as [外键名],Parent_Tab_Name as [外键表],

  [外键列]=stuff((select ','+[Parent_Col_Name] from (

  select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,

  Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name

  from sys.foreign_keys FK

  inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id

  inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'

  inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id

  and Col.parent_column_id = Parent_Col.column_id

  inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'

  inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id

  and Col.referenced_column_id = Referenced_Col.column_id

  )t where FK_Name=tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name  for xml path('')), 1, 1, ''),

  Referenced_Tab_Name as [主键表],

  [主键列]=stuff((select ','+[Referenced_Col_Name] from (

  select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,

  Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name

  from sys.foreign_keys FK

  inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id

  inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'

  inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id

  and Col.parent_column_id = Parent_Col.column_id

  inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'

  inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id

  and Col.referenced_column_id = Referenced_Col.column_id

  )t where FK_Name=tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name  for xml path('')), 1, 1, '')

  --as [外键列]

  from (

  select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,

  Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name

  from sys.foreign_keys FK

  inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id

  inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'

  inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id

  and Col.parent_column_id = Parent_Col.column_id

  inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'

  inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id

  and Col.referenced_column_id = Referenced_Col.column_id

  )tb

  group by FK_Name,Parent_Tab_Name,Referenced_Tab_Name

  --查询所有存储过程

  select Pr_Name as [存储过程], [参数]=stuff((select ','+[Parameter]

  from (

  select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter

  from sys.procedures Pr left join

  sys.parameters parameter on Pr.object_id = parameter.object_id

  inner join sys.types Type on parameter.system_type_id = Type.system_type_id

  where type = 'P'

  ) t where Pr_Name=tb.Pr_Name for xml path('')), 1, 1, '')

  from (

  select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter

  from sys.procedures Pr left join

  sys.parameters parameter on Pr.object_id = parameter.object_id

  inner join sys.types Type on parameter.system_type_id = Type.system_type_id

  where type = 'P'

  )tb

  where Pr_Name not like 'sp_%' --and Pr_Name not like 'dt%'

  group by Pr_Name

  order by Pr_Name

  --查询所有触发器

  select triggers.name as [触发器],tables.name as [表名],triggers.is_disabled as [是否禁用],

  triggers.is_instead_of_trigger AS [触发器类型],

  case when triggers.is_instead_of_trigger = 1 then 'INSTEAD OF'

  when triggers.is_instead_of_trigger = 0 then 'AFTER'

  else null

  end as [触发器类型描述]

  from sys.triggers triggers

  inner join sys.tables tables on triggers.parent_id = tables.object_id

  where triggers.type ='TR'

  order by triggers.create_date

  --查询所有索引

  select indexs.Tab_Name as [表名],indexs.Index_Name as [索引名] ,indexs.[Co_Names] as [索引列],

  Ind_Attribute.is_primary_key as [是否主键],Ind_Attribute.is_unique AS [是否唯一键],

  Ind_Attribute.is_disabled AS [是否禁用]

  from (

  select Tab_Name,Index_Name, [Co_Names]=stuff((select ','+[Co_Name] from

  ( select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind

  inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)/*索引的类型:=堆/1=聚集/2=非聚集/3=XML*/

  inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id

  inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id

  ) t where Tab_Name=tb.Tab_Name and Index_Name=tb.Index_Name for xml path('')), 1, 1, '')

  from (

  select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind

  inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)/*索引的类型:=堆/1=聚集/2=非聚集/3=XML*/

  inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id

  inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id

  )tb

  where Tab_Name not like 'sys%'

  group by Tab_Name,Index_Name

  ) indexs inner join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name

  order by indexs.Tab_Name
分享到:
本文"SqlServer常用对象查询"由远航站长收集整理而来,仅供大家学习与参考使用。更多AG亚游集团尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 广告合作 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(AG亚游集团). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类AG亚游集团.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4
姚明:青少年篮球兴趣不分城市和乡村 贸易战一触即发?特朗普或对欧盟汽车施行报复性关税 哪怕这队烂到底!NBA第一大师还是熟悉的那个人 恒大将帅赛后庆祝引围观 张文钊红色指甲油抢镜 首届新时代中国作家围棋邀请赛 曹大元为冠军颁奖 杜家毫在长沙第一干休所看望慰问老红军老干部 房地产市场红红火火 香港失业率降至20年低点 内蒙古大学生骑马上学:不会骑对不起外地人想象 握手言和?韩国防部和反萨德居民达成妥协 26岁剑桥“白富美”女博士回渝种脐橙 年赚四千万 美联储高层大讨论,加息步伐及政策调整成焦点 26岁尿毒症老师“卖笑脸”筹钱:提醒自己不放弃
关小刀任九:泽尼特有望翻盘 马竞阿森纳齐防冷 期权观察:成交量大幅增加 崔鹏再回中超感谢李指信任 努力训练有提升空间 中央广播电视总台展示区亮相中国国际影视节目展 台名嘴称若不认中国人身份 台当局没资格和大陆谈判 多位委员谈“双一流”人才争夺:不能不择手段 所有男人都是潜在强奸犯?这位大V言论让网友吵翻 韩外长:信任金正恩弃核承诺 朝美在方法上存分歧 美国对伊朗石油\"松口\"不\"松手\" 国际买家会否… 俄媒称中国共享经济领跑全球 将保持年均40%增长 外交部回应霍金去世:哀悼惋惜 他的贡献将被铭记 人民日报:广厦两次关键抢断致胜 劳森发挥失常
中国游客穿“南海九段线”服装入境越南被拦(图) 浙大原副校长褚健出狱16个月后拟获国家项目支持 ofo 3月底前估值至少28亿美元:阿里巴巴持股约12… 调查-您怎看待权健惨败?谁应背锅?还能出线吗? 西格:让中国球迷以女足为荣 我们是有野心的队伍 NBA最爱迟到的主场!火箭为让球迷早到场出大招 重庆彭水大同镇党委书记冉宇航被查(简历) 北京五环内不设烟花零售点 吐珠类烟花禁止销售 美联储会议重回焦点 美股能否安然度过? 借鸡生蛋生意点子 女生最吃香的十大职业 未来10年最赚钱行业 在家赚钱的十种方法 AG亚游集团