本自对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;