【MSSQL】常用功能

• 查看列类型

declare @table Varchar(20),
		@isnullable tinyint, -- 0-必录 1-非必录 2-不做判断
		@column_name varchar(25) -- 查看有没有这个列
Select @table = 'tbUploadGoodsImagesHash' ,@column_name = 'GoodsImages',@isnullable = 2

use cb_erp_tmp
SELECT  'YH_ERP_YHYZ' = is_nullable,column_name = name ,column_type = TYPE_NAME(system_type_id),max_length FROM sys.columns WHERE object_id=OBJECT_ID(@table)  and (name = @column_name OR @column_name = '') and (is_nullable = @isnullable OR @isnullable = 2)

• 查看表结构

declare @table Varchar(20),
		@isnullable tinyint, -- 0-必录 1-非必录 2-不做判断
		@column_name varchar(25) -- 查看有没有这个列
Select @table = 'tbAccountDetail' ,@column_name = 'data',@isnullable = 2
use cb_erp_a
select 'YH_ERP_YHYZ' = syscolumns.isnullable,syscolumns.* from syscolumns,SYSOBJECTS where  sysobjects.id = syscolumns.id and SYSOBJECTS.name = @table and SYSOBJECTS.xtype = 'U' AND (syscolumns.isnullable = @isnullable OR @isnullable = 2) AND (syscolumns.name = @column_name or @column_name = '')

• 查看存储过程结构

 declare @table Varchar(20) = 'tool_bindMember' 
use cb_erp_a
select 'YH_ERP_YHYZ'=@table,syscolumns.* from SYSOBJECTS,syscolumns where  SYSOBJECTS.id = syscolumns.id and SYSOBJECTS.name = @table and SYSOBJECTS.xtype = 'P' 

• 查看函数结构

declare @table Varchar(20) = 'uf_getstockamount' 
use cb_erp_a--YH_ERP_YHYZ
select 'YH_ERP_YHYZ'=@table,syscolumns.* from SYSOBJECTS,syscolumns where  SYSOBJECTS.id = syscolumns.id and SYSOBJECTS.name = @table and SYSOBJECTS.xtype = 'FN' 

• 查看SQL Server某个存储过程的执行历史

SELECT TOP 100 db_name(d.database_id) as DBName,
    s.name as 存储名称,
    s.type_desc as 存储类型,
    d.cached_time as SP添加到缓存的时间,
    d.last_execution_time as 上次执行SP的时间,
    d.last_elapsed_time as [上次执行SP所用的时间（微妙）],
    d.total_elapsed_time as [完成此SP的执行所用的总时间（微妙）],
    d.total_elapsed_time/d.execution_count as [平均执行时间（微妙）],
    d.execution_count as 自上次编译以来所执行的次数
FROM SYS.procedures S JOIN SYS.dm_exec_procedure_stats D
    ON S.object_id=D.object_id
WHERE S.NAME='UP_TransferWenxuanGoodsInfo'
ORDER BY D.total_elapsed_time/D.execution_count DESC

• 存储过程参数

SELECT
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'UP_TransferWenxuanGoodsInfo' and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC

• 存储过程创建、修改、最后执行日期和执行次数

SELECT a.name                AS 存储过程名称,
       a.create_date         AS 创建日期,
       a.modify_date         AS 修改日期,
       b.cached_time         AS 缓存时间,
       b.last_execution_time AS 最后执行日期,
       b.execution_count     AS 执行次数
FROM sys.procedures a
       LEFT JOIN sys.dm_exec_procedure_stats b ON a.object_id = b.object_id AND b.database_id = '7'
WHERE a.is_ms_shipped = 0 and a.name = 'UP_TransferWenxuanGoodsInfo'
ORDER BY b.execution_count desc

• 数据库中所有的存储过程

SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容
FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.ID
WHERE sc.TEXT LIKE '%tmp%'
order by 1

• 查看SQL Server历史执行的语句

SELECT TOP 1000 
QS.creation_time, 
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1, 
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) 
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1 
) AS statement_text, 
ST.text, 
QS.total_worker_time, 
QS.last_worker_time, 
QS.max_worker_time, 
QS.min_worker_time 
FROM 
sys.dm_exec_query_stats QS 
CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST 
WHERE 
QS.creation_time BETWEEN '2021-12-17 12:00:00' AND '2021-12-17 13:10:00' 
AND ST.text LIKE '%Up_GetMemberList%' 
ORDER BY 
QS.creation_time DESC

• 哪些存储过程用了表 edi_t_dm_dm_mapping

SELECT DISTINCT
	b.name,
	b.type
FROM	dbo.syscomments a,
		dbo.sysobjects b
WHERE a.id = b.id
AND b.xtype = 'p'
AND a.text LIKE '%edi_t_dm_dm_mapping%'
ORDER BY name

• 游标的使用

 DECLARE cur_trans CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
        SELECT  trans_id ,
                ag_id
        FROM    #tmp_TransformFJ;
    OPEN cur_trans;
    FETCH NEXT FROM cur_trans INTO @s_trans_id, @s_ag_id;
    WHILE @@FETCH_STATUS = 0
        BEGIN                      
               FETCH NEXT FROM cur_trans INTO @s_trans_id, @s_ag_id;
        END;
    CLOSE cur_trans;
    DEALLOCATE cur_trans;

• 获取表中的所有列

select [COLUMNS_NAME]=columns.name ,
[TYPES_NAME]=types.name ,
[COLUMNS_PRECISION]=columns.precision ,
[COLUMNS_SCALE]=columns.scale ,
[COLUMNS_MAX_LENGTH]=columns.max_length 
from sys.tables  
inner join sys.columns on sys.tables.object_id=sys.columns.object_id
inner join sys.types on sys.types.system_type_id= sys.columns.system_type_id
	and is_user_defined=0
	and sys.tables.name<>'sysname'
left outer join sys.extended_properties 
on sys.extended_properties.major_id=sys.tables.object_id
	and sys.extended_properties.minor_id=sys.columns.column_id
	and sys.extended_properties.name='MS_Description'
where sys.tables.name='fj_pici_item'
order by sys.columns.column_id

• 增加索引

 IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE id = OBJECT_ID('db_express_data') AND name = 'ix_db_express_data_trans_id')
CREATE NONCLUSTERED INDEX ix_db_express_data_trans_id ON db_express_data(trans_id)

• 控制数据表中多个列中，每一个单独列可以重复，多个列组合到一起就是唯一的

ALTER TABLE db_product 
add constraint h_name_h_isbn_h_output_price 
unique(h_name,h_isbn,h_output_price)

• 对象创建时间和修改时间

select * FROM sys.all_objects where type_desc = N'SQL_STORED_PROCEDURE'  and  name = 'up_autopick_insert'