sql server 系统表的作用
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 = @TblName13 select @ColuID = colid from syscolumns where id = @TblID and name = 'UserID'1415 select A.*,B.Name as F_TblName,C.Name as F_ColName into #TmpTbl16 from 17 sysforeignkeys as A,sysobjects as B, syscolumns as C 18 where 19 A.rkeyid = @TblID20 And21 A.rkey = @ColuID22 And23 B.id = fkeyid24 And25 C.id = fkeyid26 And27 C.colid = fkey2829 declare @tabname varchar(40),@colName varchar(20),@sqlStr varchar(1024)30 31 DECLARE TabName_Cursor CURSOR FOR32 SELECT F_TblName, F_ColName FROM #TmpTbl33 OPEN TabName_Cursor34 FETCH TabName_Cursor into @tabname,@colName35 WHILE @@FETCH_STATUS = 036 BEGIN37 select @sqlStr ='Update '+@tabname+' Set '+@colName+' ='+ "'"+@Value +"'"38 --print @sqlstr39 exec(@sqlstr)40 FETCH TabName_Cursor into @tabname,@colName41 END42 CLOSE TabName_Cursor43 DEALLOCATE TabName_Cursor44END45GO46继续
--删除某主表对应子表记录 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 = @TblName13 --select @TblID =OBJECT_ID ( 'T_User' )14 select @ColuID = colid from syscolumns where id = @TblID and name = 'UserID'1516 select A.*,B.Name as F_TblName,C.Name as F_ColName into #TmpTbl17 from 18 sysforeignkeys as A,sysobjects as B, syscolumns as C 19 where 20 A.rkeyid = @TblID21 And22 A.rkey = @ColuID23 And24 B.id = fkeyid25 And26 C.id = fkeyid27 And28 C.colid = fkey2930 declare @tabname varchar(40),@colName varchar(20),@sqlStr varchar(1024)31 32 DECLARE TabName_Cursor CURSOR FOR33 SELECT F_TblName, F_ColName FROM #TmpTbl34 OPEN TabName_Cursor35 FETCH TabName_Cursor into @tabname,@colName36 WHILE @@FETCH_STATUS = 037 BEGIN38 select @sqlStr ='delete '+@tabname+' where '+@colName+' ='+ "'"+@Value +"'"39 --print @sqlstr40 exec(@sqlstr)41 FETCH TabName_Cursor into @tabname,@colName42 END43 CLOSE TabName_Cursor44 DEALLOCATE TabName_Cursor45END来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7192349/viewspace-1001499/ ，如需转载，请注明出处，否则将追究法律责任。
