资源描述：
《实用sql语句：查询结果导出到excel,收缩数据库》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、--查询结果导出到excelSqlServerexecmaster..xp_cmdshell'bcp"select*frommydatabase.dbo.mytable"queryoutc:temp.xls-c-q-S"."-U"sa"-P"1"'--excel导入到SqlServerBULKINSERTtemp1FROM'c:temp1.xls'--收缩数据库--首先截断事务日志backuplogmydatabasewithno_log--收缩数据库dbccshrinkdatabase('mydatabase',0)--查SqlServer视图sqlselecttextfromsy
2、scommentswhereid=object_id('reportsbaseview')select*frominformation_schema.views--查SqlServer:表名select*frominformation_schema.tableswheretable_namelike'%MYTABLE%'--查Oracle:表名select*fromsys.all_tableswheretable_name='MYTABLE'--查Sqlserver列名select*frominformation_schema.columnswheretable_name=‘MYTABL
3、E’--查Orable:列名select*fromsys.all_tab_colswheretable_name='MYTABLE'--查Sqlserver列描述SELECT*FROM::fn_listextendedproperty(NULL,'user','dbo','table',‘MYTABLE’,'column',default)--查Orable:列描述select*fromsys.all_col_commentswheretable_name='MYTABLE'--为查询结果添加序号（pkId必须是整数类型）selectnumber1=(selectcount(userId
4、)fromtusersetast2wheret2.pkId<=t1.pkId),userId,setNamefromtusersetast1--插入100条测试记录declare@iintset@i=500while(@i<600)begininsertintoMYTABLE(invitesetid,invitesetno,invitesetname,managerid,projectid,invitesetstatus,projecttypeid)values(@i,@i,@i,'100001',136,0,11)set@i=@i+1end--查询每个表有几条记录declare@col
5、Idvarchar(50)DECLAREdetailCustom_CursorCURSORFORselecttop90table_nameastableNamefrominformation_schema.tablesorderbytableName--selecttable_nameastableNamefrominformation_schema.tableswheretable_namenotin(selecttop90table_namefrominformation_schema.tablesorderbytable_name)OPENdetailCustom_CursorFE
6、TCHNEXTFROMdetailCustom_Cursorinto@colIdbegintrant1declare@sqlvarchar(8000)set@sql=''WHILE@@FETCH_STATUS=0BEGINif(len(@sql)<=7800)beginset@sql=@sql+'select'''+@colId+'''astableName,count(*)asdatafrom'+@colIdset@sql=@sql+'unionall'endFETCHNEXTFROMdetailCustom_Cursorinto@colIdENDset@sql=@sql+'selec
7、t''-1'',-1'exec(@sql)committrant1CLOSEdetailCustom_CursorDEALLOCATEdetailCustom_CursorC#与sql相关1,取1条sql语句——除了某几列，其他列的都查出来，在aspx页面中写：<%string[]temp1=newstring[2]{"InviteId","SELFDEFINEDINVITEID"};stringtemp=GetExtraSql("
