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

-- 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