《SQL经典实例》第五章 元数据查询

--5.1 列出模式中的所有表
    select table_name
    from information_schema.tables
    where table_schema = 'dbo

--5.2 列出表中的列
    select column_name, data_type, ordinal_position
    from information_schema.columns
    where table_schema = 'dbo'
    and table_name   = 'EMP'

--5.3 列出表的索引列
    select a.name table_name,
        b.name index_name,
        d.name column_name,
        c.index_column_id
   from sys.tables a,
        sys.indexes b,
        sys.index_columns c,
        sys.columns d
   where a.object_id = b.object_id
         and b.object_id = c.object_id
         and b.index_id  = c.index_id
         and c.object_id = d.object_id
         and c.column_id = d.column_id
         and a.name      = 'EMP'

--5.4 列出表的约束
     select a.table_name,
         a.constraint_name,
         b.column_name,
         a.constraint_type
     from information_schema.table_constraints a,
          information_schema.key_column_usage b
     where a.table_name      = 'EMP'
           and a.table_schema    = 'dbo'
           and a.table_name      = b.table_name
           and a.table_schema    = b.table_schema
           and a.constraint_name = b.constraint_name

--5.5 列出没有相应索引的外键
    SELECT
	fkeys.table_name,
	fkeys.constraint_name,
	fkeys.column_name,
	ind_cols.index_name 
FROM
	(
	SELECT
		a.object_id,
		d.column_id,
		a.NAME table_name,
		b.NAME constraint_name,
		d.NAME column_name 
	FROM
		sys.TABLES a
		JOIN sys.foreign_keys b ON ( a.NAME = 'EMP' AND a.object_id = b.parent_object_id )
		JOIN sys.foreign_key_columns c ON ( b.object_id = c.constraint_object_id )
		JOIN sys.COLUMNS d ON ( c.constraint_column_id = d.column_id AND a.object_id = d.object_id ) 
	) fkeys
	LEFT JOIN (
	SELECT
		a.NAME index_name,
		b.object_id,
		b.column_id 
	FROM
		sys.indexes a,
		sys.index_columns b 
	WHERE
		a.index_id = b.index_id 
	) ind_cols ON ( fkeys.object_id = ind_cols.object_id AND fkeys.column_id = ind_cols.column_id ) 
WHERE
	ind_cols.index_name IS NULL

--

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值