SQL SERVER学习实例总结
笔名：北方天空<1>成功获取表字段1SelectNamefrom SysColumns Where id=Object_Id(Ingnet_wuliao1_nowuse)<2>成功获取数据库1SelectNameFROM Master.SysDatabases<3>成功获取数据库表1selectnamefrom sysobjects wheretype=UXType=U:表示所有用户表;XType=S:表示所有系统表;<4>成功判断某列是否标识列1SELECTCOLUMNPROPERTY(OBJECT_ID(a),id,IsIdentity)<5>成功exec函数用法1declare a varchar(128)set a=aexec(select * from + a)<6>成功获取标识列最大值1SELECTIDENT_CURRENT(ingnet_wuliao1)<7>成功向标识列中插入数据1SETIDENTITY_Insert a ONinsertinto a(id)values(8)setIDENTITY_INSERT a OFF<8>成功获取某表标识列名称1SELECT COLUMN_NAME FROMINFORMATION_SCHEMA.columnsWHERE TABLE_NAME=aANDCOLUMNPROPERTY(OBJECT_ID(a),COLUMN_NAME,IsIdentity)=1<9>成功查询标识列值1SELECT*FROM a WHEREIDENTITYCOL=2<10>成功获取表字段2Select*from SysColumns where id=(selecttop 1 id from sysobjects wheretype=Uandname=ingnet_fees_in_time_today)<11>成功获取所有用户表字段1Select*from SysColumns where id in(select id from sysobjects wheretype=U)<12>成功notin用法1Select*from SysColumns where(id in(select id from sysobjects wheretype=U)and(charindex(id,name)> 0)and(namenotin(id,companyid,storageid,maxid,userid,objectid,fbRenId,fbDeptId,AddUserId,userGroupId,Refer_id)<13>成功notin用法2Select*from SysColumns where(id in(select id from sysobjects wheretype=U)and(charindex(id,name)> 0)and(not(namein(id,companyid,storageid,maxid,userid,objectid,fbRenId,fbDeptId,AddUserId,userGroupId,Refer_id)<14>成功查询所有视图1select*from sysobjects where xtype=V<15>成功查询所有存储过程1select*from dbo.sysobjects whereOBJECTPROPERTY(id, NIsProcedure)= 1 <16>成功创建触发器1createtrigger trigger_test on aforupdateasselect*from b-当在表a上面执行update时将会查询表b中内容<17>成功删除触发器1droptrigger trigger_test<18>成功创建函数1createfunction test_function(a varchar(128),b int)returns re table(id int,name1 varchar(128)asbegininsertinto re(name1)values(a)returnend成功调用：select*from test_function(American,4)<19>成功删除函数1dropfunction test_function<20>成功创建函数2createfunction test_function(a varchar(128),b int)returns re table(id int,name1 varchar(128)asbegininsertinto re(name1,id)values(a,b)returnend成功调用：select*from test_function(American,4)<21>成功创建函数3createfunction test_function(a varchar(128),b int)returnsvarchar(128)asbeginreturn aend成功调用：select ingnet_logistics.dbo.test_function(American,4) 数据库.dbo.函数名<22>成功创建函数4createfunction test_function(a varchar(128),b int)returnsvarchar(128)asbegindeclare str varchar(128)set str=areturn strend成功调用: select ingnet_logistics.dbo.test_function(American,4)<23>成功创建函数5createfunction test_function(a varchar(128),b int)returnsintasbegindeclare int intset int=breturn intend成功调用：select ingnet_logistics.dbo.test_function(American,24)<24>成功保存阿拉伯语1数据库表字段必须为：nchar,nvarchar,ntext字段setlanguageArabicinsertinto test(a)values(N 2 : )<25>成功保存阿拉伯语2数据库表字段必须为：nchar,nvarchar,ntext字段insertinto test(a)values(N 2 : )<26>成功保存阿拉伯语1数据库表字段必须为：nchar,nvarchar,ntext字段SqlConnection conn = newSqlConnection("server=tt;uid=sa;pwd=ss;database=ddd1"); conn.Open();SqlCommand com1 = newSqlCommand(); com1.Connection = conn; com1.CommandType = CommandType.Text; com1.CommandTimeout = 0; com1.CommandText = "insert into test(a) values(N" + TextBox1.Text + ")" com1.ExecuteNonQuery();<27>成功获取所有用户数据库1select*from master.sysdatabases wherenamenotin(master,tempdb,model,msdb,)<28>成功生成删除所有表的sql语句1insertinto test(a)selecttruncate table +namefrom sysobjects Where(xtype=U)and(namenotin(XT_ZhaoBiao,table_user,table_zhiwei,table_bumenzhiwei,Table_quanxianxinxi,table_QuanXian)<29>删除某数据库所有表sql语句1truncatetable testinsertinto test(a)selecttruncate table +namefrom sysobjects Where(xtype=U)and(namenotin(XT_ZhaoBiao,table_user,table_zhiwei,table_bumenzhiwei,Table_quanxianxinxi,test,table_QuanXian,table_bumen)declare tt cursorforselect a from testopen ttdeclare datafun varchar(3000)fetch next from tt into datafunexec(datafun)while(fetch_status<>-1)beginfetch next from tt into datafunprint datafunexec(datafun)endclose ttdeallocate tt<30>成功获取表约束1sp_helpconstraint ingnet_materialinfo<31>获取某表外键约束1select * from sys.foreign_keys 从系统试图sys.foreign_keys可以获取指定数据库中的外键约束的基本信息.返回的列中：name :外键约束的名称。 object_id 外键约束对象的ID . Schema_id 外键约束所属的架构ID parent_object_id 约束所属表的ID . create_date 创建约束的日期和时间 modify_date 修改约束属性的日期和时间 refe
