实用sql语句：查询结果导出到excel_收缩数据库

--查询结果导出到excelSqlServerexecmaster..xp_cmdshell'bcpselect*frommydatabase.dbo.mytablequeryoutc:\temp.xls-c-q-S.-Usa-P1'--excel导入到SqlServerBULKINSERTtemp1FROM'c:\temp1.xls'--收缩数据库--首先截断事务日志backuplogmydatabasewithno_log--收缩数据库dbccshrinkdatabase('mydatabase',0)--查SqlServer视图sqlselecttextfromsyscommentswhereid=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=‘MYTABLE’--查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)fromtusersetast2wheret2.pkId=t1.pkId),userId,setNamefromtusersetast1--插入100条测试记录declare@iintset@i=500while(@i600)begininsertintoMYTABLE(invitesetid,invitesetno,invitesetname,managerid,projectid,invitesetstatus,projecttypeid)values(@i,@i,@i,'100001',136,0,11)set@i=@i+1end--查询每个表有几条记录declare@colIdvarchar(50)DECLAREdetailCustom_CursorCURSORFORselecttop90table_nameastableNamefrominformation_schema.tablesorderbytableName--selecttable_nameastableNamefrominformation_schema.tableswheretable_namenotin(selecttop90table_namefrominformation_schema.tablesorderbytable_name)OPENdetailCustom_CursorFETCHNEXTFROMdetailCustom_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+'select''-1'',-1'exec(@sql)committrant1CLOSEdetailCustom_CursorDEALLOCATEdetailCustom_CursorC#与sql相关1,取1条sql语句——除了某几列，其他列的都查出来，在aspx页面中写：%string[]temp1=newstring[2]{InviteId,SELFDEFINEDINVITEID};stringtemp=GetExtraSql(tcontent,temp1);Response.Write(temp);%protectedstringGetExtraSql(stringtableName,string[]exceptionColumns){stringreturnString=String.Empty;stringsql=selecttop1*from+tableName;DataSettemp=wdxl.Commfile.Dblib.GetDataSet(sql);if(temp!=null){sql=select;DataTabletableObj=temp.Tables[0];for(inti=0;itableObj.Columns.Count;i++){//如果找不到则添加if(!StringInArray(exceptionColumns,tableObj.Columns[i].ToString())){sql+=tableObj.Columns[i]+,;}}intflag=sql.Length;sql=sql.Substring(0,flag-1);sql+=from+tableName;}returnString=sql;returnreturnString;}protectedboolStringInArray(string[]arrayObj,stringdata){boolreturnValue=false;for(inti=0;iarrayObj.Length;i++){if(arrayObj[i].ToUpper()==data.ToUpper()){returnValue=true;break;}}returnreturnValue;}--查询另外一个数据库服务器的表数据:SELECT*FROMOPENDATASOURCE('SQLOLEDB','DataSource=192.168.0.12;database=mydatabase;userid=sa;Password=1').mydatabase.dbo.mytable--在sqlserver2005执行上面语句可能报权限错误，需要开启一下相关参数：execsp_configure'showadvancedoptions',1RECONFIGUREWITHOVERRIDEexecsp_configure'AdHocDistributedQueries',1RECONFIGUREWITHOVERRIDE--另一种方式：用链接服务器查询另外一个数据库服务器的表数据--建立链接服务器EXECsp_addlinkedserver'mycomputer','','MSDASQL',NULL,NULL,'DRIVER={SQLServer};SERVER=192.168.0.203;UID=sa;PWD=1;'GO--建立链接服务器登录映射execsp_addlinkedsrvlogin@rmtsrvname='mycomputer',@useself='false',@locallogin='Administrators',@rmtuser='sa',@rmtpassword='1'select*frommycomputer.mydatabase.dbo.users--SqlServer批量改某一列的类型，temp1为零时表declare@col1varchar(50)declare@col2varchar(50)DECLAREdetailCustom_CursorCURSORFORselectcolumn_namecol1,table_namecol2frominformation_schema.columnswheredata_type='decimal'OPENdetailCustom_CursorFETCHNEXTFROMdetailCustom_Cursorinto@col1,@col2begintrant1WHILE@@FETCH_STATUS=0BEGINbegindeclare@sqlvarchar(255)set@sql='altertable'+@col2+'altercolumn'+@col1+'intnull'insertintotemp1values(@sql)endFETCHNEXTFROMdetailCustom_Cursorinto@col1,@col2ENDcommittrant1CLOSEdetailCustom_CursorDEALLOCATEdetailCustom_Cursor--为查询结果添加一列序号Select(selectCount(*)fromFIELDDICTIONARYTwhereT.FIELDDICTIONARYid=FIELDDICTIONARY.FIELDDICTIONARYid)asNbr,*FromFIELDDICTIONARYorderbynbrasc--Oracle中的newid()SELECTSYS_GUID()FROMDUAL--监控oracle中占用磁盘I/O较高的sql语句selecta.username,b.block_gets,b.consistent_gets,b.physical_reads,b.block_changes,b.consistent_changes,c.sql_textfromv$sessiona,v$sess_iob,v$sqltextcwherea.sid=b.sidANDa.sql_address=c.addressANDa.usernameISNOTNULLorderbya.username,c.sql_id,c.piece--Oracle创建及获取表的描述，及字段描述信息--创建表描述COMMENTONTABLEmyTableis'表的描述信息'--创建字段描述COMMENTONCOLUMNmyTable.IDis'字段的描述信息'--取得表描述select*fromuser_tab_commentswherecommentsisnotnull--取得字段描述select*fromuser_col_commentswherecommentsisnotnull--SqlServer创建及获取表的描述，及字段描述信息--创建表描述EXECsp_addextendedproperty'描述类别(可自定义)','表的描述信息','user',dbo,'table',myTableName,null,null--创建字段描述EXECsp_addextendedproperty'描述类别(可自定义)'','字段的描述信息','user',dbo,'table',myTableName,'column',myColumnName--获取表描述信息SELECT*FROM::fn_listextendedproperty(NULL,'user','dbo','table','myTableName',null,default)--获取字段描述信息SELECT*FROM::fn_listextendedproperty(NULL,'user','dbo','table',