SQL Server应用知识整理

本自对DataBase不是很了解，只是工作中接触到一些SQL server的应用，遂把几个琐碎的知识整理了一下。

Q1. 如何得到table A中的字段Data在哪些存储过程中出现？

A1.

 select OBJECT_NAME(id) from syscomments where text like '%Data%'

Q2.如何得到字段CardID在数据库中哪些表中？

A2.

select t.name from sys.tables t inner join sys.columns c on t.object_id = c.object_id where c.name like '%CardID%'

Q3.迁移数据库（例如：从一块磁盘到另外磁盘）

A3.

ALTER DATABASE tempdb modify file (name = tempdev, filename = 'E:\Pathtempdb.mdf' ) 
ALTER DATABASE tempdb modify file (name = templog, filename = 'E:\Pathtemplog.ldf' )

Q4.如何得到数据库中table所占空间大小？

A4.

exec sp_spaceused
exec sp_MSforeachtable "exec sp_spaceused '?'" 

--For details, use this one
create table #t(name varchar(255), rows bigint, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))
exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'" 
select * from #t
drop table #t

Q5:Get out data from table A, and set the bit indicate this date has been read. 

Get out data condition as follows: 

open v_cursor for 
      select * from (select T1.* from T1 where A1=0 order by B1 desc) where rownum<=100; 

update 100 record in this table, set A1=1, how to update it?
A5:cursor method.

  declare 
     cursor o_cursor is 
     select * from (select sendmt.* from sendmt where processflag=0 order by priority desc) where rownum<maxrow; 
   begin 
     for v_cursor in o_cursor 
     loop 
     	update sendmt set processflag=1 where  seq1||seq2||seq3=v_cursor.seq1||v_cursor.seq2||v_cursor.seq3;  
     	commit; 
     end loop; 
   end;