Jason SQL Server

讀取SQL的SCHEMA

EXEC sp_tables

/*資料表*/
Select * From SysObjects Where xType='U' Order By Name

/*欄位*/
Select * From SysObjects A Inner Join SysColumns B On A.ID=B.ID Where A.xType='U' Order By A.Name,ColID

/*讀取SQL 資料表欄位結構的SQL 語法*/
Select A.Name As TableName,B.ColOrder As ColOrder,B.Name As ColName,C.Name As ColType,B.Length As ColLen,B.XPrec As ColPrecision,B.XScale As ColScale
From (SysObjects A Inner Join SysColumns B On A.ID=B.ID) Inner Join SysTypes C On B.XType=C.XType
Where A.XType='u'
Order By A.Name,B.ColOrder
Jason SQL Server

如何查詢SP內容

[SQL] 取出 MS SQL 預存程式(Stored Procedure) 的文字內容
如何在在所有的存儲過程中找一個關健字

select distinct b.name, b.xtype, object_name(b.parent_obj) as parent_name
from syscomments a, sysobjects b
where a.text like '%查詢文字%' and b.id=a.id and b.status>=0 and
      (b.xtype='P' or b.xtype='TR' or b.xtype='V' or b.xtype='FN')
order by b.xtype, object_name(b.parent_obj), b.name

其中在 SysObject 裡

Name ==> 代表物件的名字
Table, Primary Key, Trigger, Procedure, View, Function name

xtype ==> 代表物件的類別
U = Table, PK = Primary Key, TR = trigger, P = procedure, V = view, FN = function

parent_name:
表示 trigger 或 PKey 的 table name

Jason SQL Server

考題

問題：
台北市某國小四年級月考題目

  A B C D E
X             A
--------------
E E E E E E

請問ABCDE各代表那一個阿拉伯數字….(條件:數字不能重覆)

IF OBJECT_ID('tempdb..#TMP') is not null
	drop table #TMP

select '0' val 
into #TMP --產生0~9的結果集
union 
select '1'
union 
select '2'
union 
select '3'
union 
select '4'
union 
select '5'
union 
select '6'
union 
select '7'
union 
select '8'
union 
select '9'

Declare @result BIT = 0 --1:true / 0:false
Declare @CNT INT = 0

IF OBJECT_ID('tempdb..#TMP_3') is not null
	begin
		DROP TABLE #TMP_3
		
		select '0' A
			,'1' B
			,'2' C
			,'3' D 
			,'4' E 
			,'01234' X
		into #TMP_3	 --所有符合的結果		
	end
	

while @result <> 1
	BEGIN
		IF OBJECT_ID('tempdb..#TMP_1') is not null
			DROP TABLE #TMP_1					
		
		SELECT top 5 val 
			, IDENTITY(INT,1,1) RID
		into #TMP_1 --亂數取得5筆
		FROM #TMP
		ORDER BY NEWID()			
		
		
		IF OBJECT_ID('tempdb..#TMP_2') is not null
			DROP TABLE #TMP_2
			
		select [1] as '1'
			, [2] as '2'
			, [3] as '3'
			, [4] as '4'
			, [5] as '5'					
		into #TMP_2 --改為橫式
		from
			(select val,rid from #TMP_1) as p
		pivot
			(
			max(val)
			for rid in ([1], [2], [3], [4], [5]) 
			) as pt
		
		--select * from #TMP_3 order by X
		
		insert into #TMP_3
		select *, [1]+[2]+[3]+[4]+[5]
		from #TMP_2 T
		where T.[1]+T.[2]+T.[3]+T.[4]+T.[5] not in (select X from #TMP_3)
			
		if @@ROWCOUNT = 0 
		begin 
			set @CNT = @CNT+1
		end 
		
		if @CNT>50 --若找不到50次則EXIT
		begin
			set @result = 1 
		end
		
	END
	
select *									
from #TMP_3 
where [A]*(([A]*10000) + ([B]*1000) + ([C]*100) + ([D]*10) + [E]) = [E]*111111 

--輸出結果
select 7*((7*10000) + (9*1000) + (3*100) + (6*10) + 5)
Jason 資料庫

逐筆向前回寫的SQL

SQL：

drop table #tmp

select '20120301' datadate
	, '020' brno
	, null amt
	, 1 rid
into #tmp	 

insert into #tmp
select '20120302','020',null ,2
union all
select '20120303','020','333' ,3
union all
select '20120304','020',null , 4
union all
select '20120305','020','555' ,5
union all
select '20120306','020','666' ,6

select * from #tmp order by rid
--select * into #tmp_1 from #tmp
select * from #tmp_1 order by rid

update A set A.amt= B.amt
from #tmp A
left join #tmp B
on 1=1
where  b.rid > A.rid and B.brno = A.brno
and B.amt is not null and A.amt is null

結果：