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，然后删除所有子表的该记录，下周一实现。
实现上边所说功能的俩存储过程

1 -- 更新某主表下边对应的外键值 2 3 CREATE PROCEDURE Proc_UpdateFroeKeyVal 4 @TblName varchar ( 50 ), 5 @FroeKeyName varchar ( 50 ), 6 @Value varchar ( 50 ) 7 AS 8 9 BEGIN 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 44 END 45 GO 46

继续

1 -- 删除某主表对应子表记录 2 3 CREATE PROCEDURE Proc_DelFroeKeyVal 4 @TblName varchar ( 50 ), 5 @FroeKeyName varchar ( 50 ), 6 @Value varchar ( 50 ) 7 AS 8 9 BEGIN 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 45 END

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7192349/viewspace-1001499/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7192349/viewspace-1001499/