1、查询数据库所有主外键表

Use  数据库名

go
select  OBJECT_NAME(a.[parent_object_id]) 外键表,
OBJECT_NAME(a.[referenced_object_id]) 主键表
from sys.foreign_keys  AS a

 

2、通过“字段名”查找对应 “表名称”

Use  数据库名

go
select   a.name  字段名 ,b.name  表名 from   syscolumns     a   inner   join   sysobjects   b   on   a.id=b.id
where   a.name   =  '字段名'

 

3、查询数据库所有类型的“表名”

select name 触发器 from sysobjects where xtype='TR' --所有触发器
select name  存储过程 from sysobjects where xtype='P' --所有存储过程
select name 视图  from sysobjects where xtype='V' --所有视图
select name 表名 from sysobjects where xtype='U' --所有表
select name 系统表名 from sysobjects where xtype='S' --所有表

 

5、查询“表”的所有字段名

SELECTname 字段名FROMSysColumnsWHEREid=Object_Id('表名')

 

6、查询数据库所有表（where指定表）所有字段相关属性

SELECTOBJECT_NAME(a.id)表名,a.name字段名,b.name字段类型,
a.isnullable是否为空,a.length字段长度
FROMsyscolumnsajoinsystypesbona.xusertype=b.xusertype

 

 

7、查询数据库的所有主外键相关属性

SELECTT1.name[约束名],T3.name[外键表],T5.name[主键表],T4.name[外键列名],T6.name[主键列名]
FROMSYS.FOREIGN_KEYST1JOINSYS.FOREIGN_KEY_COLUMNST2ONT1.object_id=T2.constraint_object_id 
JOINSYS.SYSOBJECTST3ONT1.parent_object_id=T3.id  
JOINSYS.SYSCOLUMNST4ONT2.parent_object_id=T4.idANDT2.parent_column_id=T4.colid  
JOINSYS.SYSOBJECTST5ONT1.referenced_object_id=T5.id  
JOINSYS.SYSCOLUMNST6ONT2.referenced_object_id=T6.idANDT2.referenced_column_id=T6.colid

 

8、查询所有关于索引属性列表

SELECTOBJECT_NAME(sysobjects.parent_obj)表名,--sysobjects.id objectId,
 
sysobjects.name索引名,sysobjects.xtypeAS索引类型,
 
syscolumns.nameAS索引字段
 
FROMsysobjectsINNERJOINsysconstraints
 
ONsysobjects.xtypein('F','PK','UQ','D','C')
 
ANDsysobjects.id=sysconstraints.constid
 
LEFTOUTERJOINsyscolumnsONsysconstraints.id=syscolumns.id

 

9、外键语句创建、同时创建不了外键

alter table 外键表名 add constraint 约束名称 foreign key (外键字段) references 主键表名(约束列名)
 
 
alter table dbo.HIS_patient_out_cure add constraint FK_HIS_patient_out_cure_HIS_patient_account foreign key (s_ic_no) references dbo.HIS_patient_account(s_ic_no)

剔除跟外键无关的值

delete from HIS_patient_out_cure where HIS_patient_out_cure.s_ic_no not in(
select b.s_ic_no from HIS_patient_out_cure inner join HIS_patient_account b on HIS_patient_out_cure.s_ic_no=b.s_ic_no)


10、通过语句获取存储内容

sp_helptext 'dbo.sp_Nurse_care_level1' sp_helptext 存储名称

 

11、查询表结构详细信息

SELECT top 10000 obj.name AS 表名,
col.colorder AS 序号 ,
col.name AS 列名 ,
ISNULL(ep.[value], '') AS 列说明 ,
t.name AS 数据类型 ,
col.length AS 长度 ,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END AS 标识 ,
CASE WHEN EXISTS (SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid) THEN '√'
ELSE ''
END AS 主键,
CASE WHEN col.isnullable = 1 THEN '√'
ELSE ''
END AS 允许空 ,
ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = 'U'
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
--表查询条件
where obj.name       in 
('RF_MailContent','RF_MailDeletedBox','RF_MailInBox','RF_MailOutBox')ORDER BY obj.name

 
12、每次随机获取数据，通过排序newid()方法

SELECT TOP 10 *
  FROM 
表名 order by newid()