Sqlserver常见查询语句

-- 查询所有的用户表

SELECT * FROM  master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'northwind','pubs' );

-- 查询所有的模式
SELECT * FROM 数据库名.sys.schemas

--查询所有用户表的模式(去重)

SELECT DISTINCT a.name AS schema_name FROM 数据库名.sys.schemas a,数据库名..sysobjects b WHERE
 a.schema_id = b.uid  AND xtype = 'U'

-- 查询所有的用户表及其所有字段
SELECT * FROM 数据库名..sysobjects Where xtype='U'

-- 查询模式下所有表名
select a.name as schema_name,b.name as table_name from
(SELECT * FROM 数据库名.sys.schemas) a
LEFT JOIN (SELECT * FROM 数据库名..sysobjects Where xtype='U') b
ON a.schema_id=b.uid

-- 查询表名对应的模式
select c.schema_name from (select a.name as schema_name,b.name as table_name from
(SELECT * FROM 数据库名.sys.schemas) a
LEFT JOIN (SELECT * FROM 数据库名..sysobjects Where xtype='U') b
ON a.schema_id=b.uid) c where c.table_name='表名'

-- 查询所有模式及其字段
select s.name as schema_name,
s.schema_id,
u.name as schema_owner
from sys.schemas s
inner join sys.sysusers u
on u.uid = s.principal_id
order by s.name

-- 查询所有模式对应的表名,表名不为空
select a.name as schema_name,b.name as table_name from 数据库名.sys.schemas a
,数据库名..sysobjects b
where a.schema_id=b.uid and xtype='U'