SQL SERVER学习实例总结

1、笔名：北方天空成功获取表字段1SelectNamefrom SysColumns Where id=Object_Id(Ingnet_wuliao1_nowuse)成功获取数据库1SelectNameFROM Master.SysDatabases成功获取数据库表1selectnamefrom sysobjects wheretype=UXType=U:表示所有用户表;XType=S:表示所有系统表;成功判断某列是否标识列1SELECTCOLUMNPROPERTY(OBJECT_ID(a),id,IsIdentity)成功exec函数用法1declare a varchar(128)set a=aexec(select * from + a)成功获取标识列最大值1SELECTIDENT_CURRENT(ingnet_wuliao1)成功向标识列中插入数据1SETIDENTITY_Insert a ONinsertinto a(id)values(8)setIDENTITY_INSERT a OFF成功获取某表标识列名称1SELECT COLUMN_NAME FROMINFORMATIO

2、N_SCHEMA.columnsWHERE TABLE_NAME=aANDCOLUMNPROPERTY(OBJECT_ID(a),COLUMN_NAME,IsIdentity)=1成功查询标识列值1SELECT*FROM a WHEREIDENTITYCOL=2成功获取表字段2Select*from SysColumns where id=(selecttop 1 id from sysobjects wheretype=Uandname=ingnet_fees_in_time_today)成功获取所有用户表字段1Select*from SysColumns where id in(select id from sysobjects wheretype=U)成功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,

3、fbRenId,fbDeptId,AddUserId,userGroupId,Refer_id)成功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)成功查询所有视图1select*from sysobjects where xtype=V成功查询所有存储过程1select*from dbo.sysobjects whereOBJECTPROPERTY(id, NIsProcedure)= 1 成功创建触发器1createtrigger trigger_test on aforupdateasselect*from b-当在表a上面执行update时将会查询表b中内容成功删除触发器1droptrigger trig

4、ger_test成功创建函数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)成功删除函数1dropfunction test_function成功创建函数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)成功创建函数3createfunction test_function(a varchar(128),b int)returnsvarchar(128)asbegi

5、nreturn aend成功调用：select ingnet_logistics.dbo.test_function(American,4) 数据库.dbo.函数名成功创建函数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)成功创建函数5createfunction test_function(a varchar(128),b int)returnsintasbegindeclare int intset int=breturn intend成功调用：select ingnet_logistics.dbo.test_function(American,24)成功保存阿拉伯语1数据库表字段必须为：nchar,nvarchar,ntext字段setlanguageArabicin

6、sertinto test(a)values(N 2 : )成功保存阿拉伯语2数据库表字段必须为：nchar,nvarchar,ntext字段insertinto test(a)values(N 2 : )成功保存阿拉伯语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();成功获取所有用户数据库1select*from master.sysdatabases wherename

7、notin(master,tempdb,model,msdb,)成功生成删除所有表的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)删除某数据库所有表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成功获取表约束1sp_helpconstraint ingnet_materialinfo获取某表外键约束1select * from sys.foreign_keys 从系统试图sys.foreign_keys可以获取指定数据库中的外键约束的基本信息.返回的列中：name :外键约束的名称。 object_id 外键约束对象的ID . Schema_id 外键约束所属的架构ID parent_object_id 约束所属表的ID . create_date 创建约束的日期和时间 modify_date 修改约束属性的日期和时间 refe

《SQL SERVER学习实例总结》由会员北***分享，可在线阅读，更多相关《SQL SERVER学习实例总结》请在金锄头文库上搜索。