SQL系統資料

/*檢視表,預存程序內容*/
EXEC sp_helptext 'PDL_LotNumHis'

/*系統物件LIST*/
select * from sys.objects where name LIKE 'SA%' and type='U' order by name

/*資料表LIST*/
select name from sys.tables

/*檢視表LIST*/
select name from sys.views

/*TABLE欄位LIST*/
select cast(c.name as varchar(40)),convert(varchar(40),s.name),* from sys.columns c inner join sys.objects s on c.object_id=s.object_id
where s.name='UTA_MakStrList'

/*COLUMN欄位LIST*/

select c.name,s.name,* from sys.columns c inner join sys.objects s on c.object_id=s.object_id
where c.name='DpMethodName'

CURSOR/Simple

DECLARE curCur
READ_ONLY
FOR select name from sys.objects where type='U' and name like 'StknWIP%' order by name


declare @strsql nvarchar(300)
declare @objname varchar(40)
OPEN curCur
SET NOCOUNT ON
FETCH NEXT FROM curCur INTO @objname
WHILE (@@fetch_status =0)
BEGIN
set @strsql='select count(*) from '+@objname
-- PRINT 'add user defined code here'
-- eg.
PRINT @strsql
EXECUTE sp_executesql @strsql
FETCH NEXT FROM curCur INTO @objname
END

CLOSE curCur
DEALLOCATE curCur
SET NOCOUNT OFF

GO

SysColumn

select t1.name ColName,t2.name TableName from sys.columns t1 inner join sys.objects t2 on t1.object_id=t2.object_id where t1.name like 'ProcName%' ORDER BY t2.Name

CURSOR

DECLARE vend_cursor
FOR select name from sys.objects where name LIKE 'SA%' and type='U' order by name

declare @objname varchar(100)
DECLARE @SQLString nvarchar(500)
DECLARE @ParmDefinition nvarchar(500)
DECLARE @cntAffect int


OPEN vend_cursor

FETCH NEXT FROM vend_cursor into @objname
SET @SQLString = N'SELECT @cntAffectOut =count(*) from '+@objname

SET @ParmDefinition = N'@cntAffectOut int OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @cntAffectOut=@cntAffect OUTPUT
IF @cntAffect>0
BEGIN
select @objname
SET @SQLString='select top 10 * from '+@objname
EXECUTE sp_executesql @SQLString
END

WHILE (@@FETCH_STATUS = 0)
BEGIN

FETCH NEXT FROM vend_cursor into @objname
SET @SQLString = N'SELECT @cntAffectOut =count(*) from '+@objname

SET @ParmDefinition = N'@cntAffectOut int OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @cntAffectOut=@cntAffect OUTPUT
IF @cntAffect>0
BEGIN
select @objname
SET @SQLString='select top 10 * from '+@objname
EXECUTE sp_executesql @SQLString
END
END
CLOSE vend_cursor;
DEALLOCATE vend_cursor;
SET NOCOUNT OFF

AddLinkServer

USE master
--連到外部資料庫
EXEC sp_addlinkedserver @server='A07', @srvproduct='',
@provider='SQLOLEDB', @datasrc='A07'
--建立Login資料
EXEC sp_addlinkedsrvlogin 'G20', 'false', NULL, 'sa', 'sa'
--查詢已連結資料庫
EXEC sp_linkedservers
--刪除已連結資料庫
EXEC sp_dropserver ''HR'', 'droplogins'

Trigger 停用

alter table prodbasic disable trigger tu_prodbasic

apcb 08 9 0 SP3 ) APCB \CLORIS _08247 (621) APCB 00:01:12 0 個資料列

sp_who 621

spid ecid status loginame hostname blk dbname cmd request_ id

1 621 0 sleeping APCB \CLORIS _08247 G 102010020 695 APCB AWAITING COMMAND 0


kill 695