sql辅助操作

1.查询表的基本信息

SELECT *
FROM sys.extended_properties ds  
LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id  
WHERE  ds.minor_id=0 and  
tbs.name='T_Test';--表名  

 2.查询表字段的说明信息

 SELECT t.[name] AS [表名],c.[name] AS [字段名],cast(ep.[value]  

  as varchar(100)) AS [字段说明]

  FROM sys.tables AS t INNER JOIN sys.columns  

  AS c ON t.object_id = c.object_id LEFT JOIN sys.extended_properties AS ep  

  ON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE ep.class =1  

  and t.[name]='T_Test'--表名

3.SET NOCOUNT --->http://www.cnblogs.com/lmfeng/archive/2011/10/12/2208821.html

4.SET TRANSACTION ISOLATION LEVEL READ COMMITTED -->http://www.cnblogs.com/lmfeng/archive/2011/10/12/2208821.html

5.数据库添加一个用户并且给用户授权

exec sp_addlogin 'viewreader','pass'   

exec sp_adduser 'viewreader'

grant select on viewname to viewreader --试图名字

6.sql字符串切割，查询某个字符出现的第N个位置

create function fn_find(@find varchar(8000), @str varchar(8000), @n smallint)
    returns int
as
begin
    if @n < 1 return (0)
    declare @start smallint, @count smallint, @index smallint, @len smallint
    set @index = charindex(@find, @str)
    if @index = 0 return (0)
    else select @count = 1, @len = len(@find)
    while @index > 0 and @count < @n
        begin
            set @start = @index + @len
            select @index = charindex(@find, @str, @start), @count = @count + 1
        end
    if @count < @n set @index = 0
    return (@index)
end
go

declare @str varchar(200)
set @str='dsad|sdas|dsds|123'
select dbo.fn_find('|',@str,2)

7.查询存储过程的修改时间

select 
 [name]
 ,create_date
 ,modify_date 
FROM 
 sys.all_objects 
where 
 type_desc = N'SQL_STORED_PROCEDURE' 
 and modify_date >='2017-03-24 00:00:00'