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

创作编号：GB8878185555334563BT9125XW创作者：凤呜大王*--查询结果导出到excelSqlServerexec master..xp_cmdshell 'bcp "select * from mydatabase.dbo.mytable" queryout c:\temp.xls -c -q -S"." -U"sa" -P"1"'--excel导入到SqlServerBULK INSERT temp1 FROM 'c:\temp1.xls'--收缩数据库--首先截断事务日志backup log mydatabase with no_log--收缩数据库dbcc shrinkdatabase('mydatabase',0)--查SqlServer视图sqlselect text from syscomments where id=object_id('reportsbaseview')select * from information_schema.views--查SqlServer:表名select * from information_schema.tables where table_name like '%MYTABLE%'--查Oracle:表名select * from sys.all_tables where table_name = 'MYTABLE'--查Sqlserver列名select * from information_schema.columns where table_name = ‘MYTABLE’--查Orable:列名select * from sys.all_tab_cols where table_name = ' MYTABLE'--查Sqlserver列描述SELECT *FROM ::fn_listextendedproperty (NULL, 'user' , 'dbo', 'table', ‘MYTABLE’, 'colum n', default)--查Orable:列描述select * from sys.all_col_comments where table_name = ' MYTABLE'--为查询结果添加序号（pkId必须是整数类型）select number1=(select count(userId) from tuserset as t2 where t2.pkId<=t1.pkId),userI d,setName from tuserset as t1--插入100条测试记录declare @i intset @i=500while (@i<600)begininsert into MYTABLE (invitesetid,invitesetno,invitesetname,managerid,projectid,invitese tstatus,projecttypeid)values(@i,@i,@i,'100001',136,0,11)set @i=@i+1end--查询每个表有几条记录declare @colId varchar(50)DECLARE detailCustom_Cursor CURSOR FORselect top 90 table_name as tableName from information_schema.tables order by tabl eName--select table_name as tableName from information_schema.tables where table_name not in (select top 90 table_name from information_schema.tables order by table_nam e)OPEN detailCustom_CursorFETCH NEXT FROM detailCustom_Cursorinto @colId创作编号：GB8878185555334563BT9125XW创作者：凤呜大王*begin tran t1declare @sql varchar(8000)set @sql = ''WHILE @@FETCH_STATUS = 0BEGINif(len(@sql)<=7800)beginset @sql = @sql + ' select '''+@colId+''' as tableName,count(*) as data from '+@colIdset @sql = @sql + ' union all 'endFETCH NEXT FROM detailCustom_Cursorinto @colIdENDset @sql = @sql + ' select ''-1'',-1'exec(@sql)commit tran t1CLOSE detailCustom_CursorDEALLOCATE detailCustom_CursorC#与sql相关1,取1条sql语句——除了某几列，其他列的都查出来，在aspx页面中写：<%string[] temp1 = new string[2] {"InviteId","SELFDEFINEDINVITEID "};string temp = GetExtraSql("tcontent",temp1);Response.Write(temp);%>protected string GetExtraSql(string tableName,string[] exceptionColumns){string returnString = String.Empty;string sql = "select top 1 * from "+tableName;DataSet temp = mfile.Dblib.GetDataSet(sql);if(temp!=null){sql = "select ";DataTable tableObj = temp.Tables[0];for(int i=0;i<tableObj.Columns.Count;i++){//如果找不到则添加if(!StringInArray(exceptionColumns,tableObj.Columns[i].ToStrin g())){sql += tableObj.Columns[i]+",";}}int flag = sql.Length;sql = sql.Substring(0,flag-1);sql += " from "+tableName;}returnString = sql;return returnString;}创作编号：GB8878185555334563BT9125XW创作者：凤呜大王*protected bool StringInArray(string[] arrayObj,string data){bool returnValue = false;for (int i=0;i<arrayObj.Length;i++){if(arrayObj[i].ToUpper()==data.ToUpper()){returnValue = true;break;}}return returnValue;}--查询另外一个数据库服务器的表数据:SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.0.12;dat abase=mydatabase;user id=sa;Password=1').mydatabase.dbo.mytable--在sqlserver2005执行上面语句可能报权限错误，需要开启一下相关参数：exec sp_configure 'show advanced options',1RECONFIGURE WITH OVERRIDEexec sp_configure 'Ad Hoc Distributed Queries',1RECONFIGURE WITH OVERRIDE--另一种方式：用链接服务器查询另外一个数据库服务器的表数据--建立链接服务器EXEC sp_addlinkedserver'mycomputer','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=192.168.0.203;UID=sa;PWD=1;'GO--建立链接服务器登录映射exec sp_addlinkedsrvlogin@rmtsrvname='mycomputer',@useself='false',@locallogin='Administrators',@rmtuser='sa', @rmtpassword='1'select * from ers--SqlServer批量改某一列的类型，temp1为零时表declare @col1 varchar(50)declare @col2 varchar(50)DECLARE detailCustom_Cursor CURSOR FORselect column_name col1,table_name col2 from information_schema.columns where d ata_type = 'decimal'OPEN detailCustom_CursorFETCH NEXT FROM detailCustom_Cursorinto @col1,@col2begin tran t1WHILE @@FETCH_STATUS = 0BEGINbegindeclare @sql varchar(255)set @sql = ' alter table '+@col2+' alter column '+@col1+' int null'insert into temp1 values (@sql)endFETCH NEXT FROM detailCustom_Cursorinto @col1,@col2ENDcommit tran t1CLOSE detailCustom_CursorDEALLOCATE detailCustom_Cursor创作编号：GB8878185555334563BT9125XW创作者：凤呜大王*--为查询结果添加一列序号Select (select Count(*) from FIELDDICTIONARY T where T.FIE LDDICTIONARYid<=FIELDDICTIONARY.FIELDDICTIONARYid)as Nbr ,*From FIELDDICTIONARY order by nbr asc--Oracle中的newid()SELECT SYS_GUID() FROM DUAL--监控oracle中占用磁盘I/O较高的sql语句select ername,b.block_gets,b.consistent_gets,b.physical_reads,b.block_changes,b.consistent_changes,c.sql_textfrom v$session a,v$sess_io b,v$sqltext cwhere a.sid=b.sid AND a.sql_address=c.addressAND ername IS NOT NULLorder by ername,c.sql_id,c.piece--Oracle创建及获取表的描述，及字段描述信息--创建表描述COMMENT ON TABLE myTable is '表的描述信息'--创建字段描述COMMENT ON COLUMN myTable.ID is '字段的描述信息'--取得表描述select * from user_tab_comments where comments is not null--取得字段描述select * from user_col_comments where comments is not null--SqlServer创建及获取表的描述，及字段描述信息--创建表描述EXEC sp_addextendedproperty '描述类别(可自定义)', '表的描述信息', 'user', dbo, 'tab le', myTableName, null, null--创建字段描述EXEC sp_addextendedproperty '描述类别(可自定义)'', '字段的描述信息', 'user', dbo, ' table', myTableName, 'column', myColumnName--获取表描述信息SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'myTableNa me' , null, default)--获取字段描述信息SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'myTableNa me', 'column', default)--修改oracle的dbf文件的位置c:\>sqlplus /nologSQL> conn / as sysdba;SQL>shutdown immediateSQL>startup mount;SQL>SELECT FILE# ,ERROR FROM V$RECOVER_FILE;--查看是否有挂接失败数据文件SQL>select r.file# ,,r.errorfrom v$ercover_file r , v$datafile Dwhere r.file#=D.file#;--查看失败文件的路径(如果没有挂接失败的数据文件，可以直接查v$datafile) SQL>alter database rename file 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\U SERS01.DBF' TO 'D:\oracle\product\10.2.0\oradata\orcl\USERS01.DBF';--同上。