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

--查询结果导出到excel SqlServer exec master..xp_cmdshell bcp select * from mydatabase.dbo.mytable queryout c:\temp.xls -c -q -S. -Usa -P1 --excel导入到SqlServer BULK INSERT temp1 FROM c:\temp1.xls --收缩数据库 --首先截断事务日志 backup log mydatabase with no_log --收缩数据库 dbcc shrinkdatabase(mydatabase,0) --查SqlServer视图sql select 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’, column, 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),userId,setName from tuserset as t1 --插入100条测试记录 declare @i int set @i=500 while (@i600) begin insert into MYTABLE (invitesetid,invitesetno,invitesetname,managerid,projectid,invitesetstatus,projecttypeid) values(@i,@i,@i,100001,136,0,11) set @i=@i+1 end --查询每个表有几条记录 declare @colId varchar(50) DECLARE detailCustom_Cursor CURSOR FOR select top 90 table_name as tableName from information_schema.tables order by tableName --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_name) OPEN detailCustom_Cursor FETCH NEXT FROM detailCustom_Cursor into @colId begin tran t1 declare @sql varchar(8000) set @sql = WHILE @@FETCH_STATUS = 0 BEGIN if(len(@sql)=7800) begin set @sql = @sql + select +@colId+ as tableName,count(*) as data from +@colId set @sql = @sql + union all end FETCH NEXT FROM detailCustom_Cursor into @colId END