SQLServer常用SQL语句

SQLServer常用SQL语句

1、求某一表的字段名称，类型、长度：

select b.name as fieldname,c.name as typename,b.length as fieldlen 
from sysobjects a,syscolumns b,systypes c
where a.id=b.id and b.xtype=c.xtype and a.name='表名' 
order by b.colid

select  column_name,data_type ,character_maximum_length
from information_schema.columns
where table_name = '表名'
order by ordinal_position 

2、N到M条记录(要有主索引ID):

Select Top M-N * From 表 Where ID in  (Select Top M ID From 表) Order by ID Desc

3、查询用户创建的所有数据库

select * from master..sysdatabases D 
where sid not in
(select sid from master..syslogins where name='sa')

4、查看当前数据库中所有存储过程

select name as 存储过程名称
from sysobjects
where xtype='P'--视图为'V'，触发器'TR'，用户表为'U'，系统表为'S'

5、定义表变量

DECLARE @EnMeter TABLE(ID Int, ENum VARCHAR(100),EName VARCHAR(100));

6、查询用户表

select name from dbo.sysobjects where name like ''%表名部分标识%'' and OBJECTPROPERTY(id, N''IsUserTable'') = 1

7、分割字符串

ALTER function dbo.GetStrArray(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(sqlstr   varchar(100))
as 
begin
    declare @i int
    set @SourceSql=rtrim(ltrim(@SourceSql))
    set @i=charindex(@StrSeprate,@SourceSql)
    while @i>=1
    begin
        insert @temp values(left(@SourceSql,@i-1))
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        set @i=charindex(@StrSeprate,@SourceSql)
    end
    if @SourceSql<>'\'
       insert @temp values(@SourceSql)
    return 
end