查找没有主键的表、没有索引的表

-- 1、查找没有主键的表、没有索引的表

SELECT so.name as '没有主键的表'
FROM sysobjects so
WHERE so.xtype = 'U'
AND OBJECTPROPERTY(so.id , 'TableHasPrimaryKey' ) = 0
ORDER BY name

SELECT so.name as '没有索引的表'
FROM sysobjects so
WHERE so.xtype = 'U'
AND OBJECTPROPERTY(so.id , 'TableHasIndex' ) = 0
ORDER BY name


--2、查看数据库用户表使用了哪些数据类型

select d.* , b.[name] as '数据类型' from ( select distinct a.xusertype from syscolumns as a where a.id in
(select id from sysobjects where xtype='u' and status>0 ) )as d left join systypes as b
on d.xusertype=b.xusertype

--3、看使用nvarchar类型的用户表名和字段名称 xusertype 具体数值看当前数据库中系统表systypes中的记录
select sc.name as '字段名称' , so.name as '表名' from syscolumns sc inner join
sysobjects so on sc.id=so.id where sc.id in
( select id from sysobjects where xtype='u' and status>0 )
and sc.xusertype=231 order by so.name ,sc.colid

--4、查看数据库中使用外键和被引用的表

SELECT so1.name AS '外键表', so2.name AS '引用表', sc1.name AS '外键字段',
sc2.name AS '引用字段'
FROM sysforeignkeys sf INNER JOIN
sysobjects so1 ON sf.fkeyid = so1.id INNER JOIN
sysobjects so2 ON sf.rkeyid = so2.id INNER JOIN
syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid INNER JOIN
syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid