1. 如何取得一个数据表的所有列名
方法如下:先从sysobjects系统表中取得数据表的systemid,然后再syscolumns表中取得该数据表的所有列名。
SQL语句如下:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
SQL Code
Declare @objid int,@objname char(40)
set @objname = 'ColumnToRow'
--第1种方法
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
--或也可以写成
select name as 'Column_name' from syscolumns where id = @objid order by colid
--第2种方法:
Select name as 'Column_Name' from SysColumns where id = object_id(@objname) Order by colid
2. 通过SQL语句来更改用户的密码
修改别人的,需要sysadmin role
Exec Sp_password '原始密码','更改后密码','账号'
Exec sp_password null,ok,sa
3. 怎么判断出一个表的哪些字段不允许为空?
Declare @objname Varchar(50)
set @objname = 'ColumnToRow'
Select Column_Name from information_schema.Columns where is_nullable = 'No' and Table_Name = @objname
4. 如何在数据库里找到含有相同字段的表?
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
SQL Code
--查已知列名的情况
Select a.name as Columnname,b.name as tablename from SysColumns a inner join sysobjects b on a.id = b.id
and b.type = 'U' and a.name = '您要查找的字段名'
--未知列名查所有在不同表出现过的列名
Select s.name as tablename,s1.name as columnname from SysColumns s1,Sysobjects s
Where s1.id = s.id and s.Type = 'U' and Exists (Select 1 from syscolumns s2 where s1.name = s2.name and s1.id <> s2.id)
5.查询第N行数据
假设id是主键:
select *
from (select top N * from 表) aa
where not exists(select 1 from (select top N-1 * from 表) bb where aa.id=bb.id)
6.查询某一个表的字段和数据类型
select column_name,data_type
from information_schema.columns
where table_name = '表名'
7.几个用法
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
SQL Code
--查看表的属性
select * from sysObjects where [Name] = 'section'
--用法
if exists ( select * from sysObjects where [Name] = 'section' and xtype='U' )
Drop Table table1
go
Create table1 ( )
--获取所有用户表
select Name from sysobjects where xtype='u' and status>=0
--查看表的字段
select * from sysColumns c where c.id=object_id('section')
select name from syscolumns where id=object_id('表名')
--查看用户
select * From sysusers where status<>0
--查看谁引用了bbs_hits表(包括视图、存储过程、函数)
Select distinct object_name(d.id) as 'program',
o.xtype
from sysdepends d inner join sysobjects o on d.id=o.id
where object_name(depid)='bbs_hits'
--查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
--查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
8.获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
9.查询用户创建的所有数据库
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
SQL Code
select * from master..sysdatabases D
where sid not in
(select sid from master..syslogins where name='sa')
--或者
select dbid, name AS DB_NAME
from master..sysdatabases
where sid <> 0x01