sql server中系统表的作用
1:获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
2:获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
3:查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
4:查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
5:查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
6:查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns where table_name = '表名'
比如说有A表的一个主键,作为B、C、D表的外键,可能你在某些时候修改A表中的某个记录,也许这个记录被BCD表已经用到,要修改很麻烦,昨天写了几个sql语句可以方便的实现这个要求,不过下班走的急,没来得及这里整理,周一上班再看贴上,初步计划是还有一个主要的应用,是在做子母表删除的时候,应该可以写一个函数,传入主键ID,然后删除所有子表的该记录,下周一实现。
实现上边所说功能的俩存储过程
2
3CREATE PROCEDURE Proc_UpdateFroeKeyVal
4 @TblName varchar(50),
5 @FroeKeyName varchar(50),
6 @Value varchar(50)
7 AS
8
9BEGIN
10 declare @TblID varchar(50),
11 @ColuID varchar(50)
12 select @TblID = id from sysobjects where name = @TblName
13 select @ColuID = colid from syscolumns where id = @TblID and name = 'UserID'
14
15 select A.*,B.Name as F_TblName,C.Name as F_ColName into #TmpTbl
16 from
17 sysforeignkeys as A,sysobjects as B, syscolumns as C
18 where
19 A.rkeyid = @TblID
20 And
21 A.rkey = @ColuID
22 And
23 B.id = fkeyid
24 And
25 C.id = fkeyid
26 And
27 C.colid = fkey
28
29 declare @tabname varchar(40),@colName varchar(20),@sqlStr varchar(1024)
30
31 DECLARE TabName_Cursor CURSOR FOR
32 SELECT F_TblName, F_ColName FROM #TmpTbl
33 OPEN TabName_Cursor
34 FETCH TabName_Cursor into @tabname,@colName
35 WHILE @@FETCH_STATUS = 0
36 BEGIN
37 select @sqlStr ='Update '+@tabname+' Set '+@colName+' ='+ "'"+@Value +"'"
38 --print @sqlstr
39 exec(@sqlstr)
40 FETCH TabName_Cursor into @tabname,@colName
41 END
42 CLOSE TabName_Cursor
43 DEALLOCATE TabName_Cursor
44END
45GO
46
继续
2
3CREATE PROCEDURE Proc_DelFroeKeyVal
4 @TblName varchar(50),
5 @FroeKeyName varchar(50),
6 @Value varchar(50)
7 AS
8
9BEGIN
10 declare @TblID varchar(50),
11 @ColuID varchar(50)
12 select @TblID = id from sysobjects where name = @TblName
13 --select @TblID =OBJECT_ID ( 'T_User' )
14 select @ColuID = colid from syscolumns where id = @TblID and name = 'UserID'
15
16 select A.*,B.Name as F_TblName,C.Name as F_ColName into #TmpTbl
17 from
18 sysforeignkeys as A,sysobjects as B, syscolumns as C
19 where
20 A.rkeyid = @TblID
21 And
22 A.rkey = @ColuID
23 And
24 B.id = fkeyid
25 And
26 C.id = fkeyid
27 And
28 C.colid = fkey
29
30 declare @tabname varchar(40),@colName varchar(20),@sqlStr varchar(1024)
31
32 DECLARE TabName_Cursor CURSOR FOR
33 SELECT F_TblName, F_ColName FROM #TmpTbl
34 OPEN TabName_Cursor
35 FETCH TabName_Cursor into @tabname,@colName
36 WHILE @@FETCH_STATUS = 0
37 BEGIN
38 select @sqlStr ='delete '+@tabname+' where '+@colName+' ='+ "'"+@Value +"'"
39 --print @sqlstr
40 exec(@sqlstr)
41 FETCH TabName_Cursor into @tabname,@colName
42 END
43 CLOSE TabName_Cursor
44 DEALLOCATE TabName_Cursor
45END