随笔分类 - sql server
摘要：--建表 select * into tablenew from tableold
阅读全文
摘要：USE ncdb;SELECT OBJECT_NAME(object_id) AS TableName, SUM(reserved_page_count) * 8 AS TotalSizeKB, SUM(CAST(CASE WHEN index_id < 2 THEN in_row_data_pag
阅读全文
摘要：原始sql 执行半小时， 临时表gl_tmp1里大概2万条数据 select distinct ( gl_detail.pk_voucher ) from gl_detail , gl_contrast where gl_detail.pk_detail = gl_contrast.fk_detai
阅读全文
摘要：1，查看连接到某个数据库的连接select * from master.dbo.sysprocesses where dbid = DB_ID('NCCDB')*查询某个数据库用户的连接情况sp_who 'sa'2，查看数据库允许的最大连接select @@MAX_CONNECTIONS3，查看数据
阅读全文
摘要：--改名EXEC sp_rename 'old_table_name', 'new_table_name';--备份select * into dbo.t_scs_0304 from dbo.t_scs; 对表结构字段进行修改： 添加列：alter table 表名 add 列名 varchar(5
阅读全文
摘要：查看统计信息 SELECT object_id , OBJECT_NAME(object_id) AS TableName , name AS StatisticsName , auto_created FROM sys.stats where objectproperty(object_id,'I
阅读全文
摘要：SELECT OBJECT_NAME(IDX.object_id) Table_Name , IDX.name Index_name , PAR.rows NumOfRows , IDX.type_desc TypeOfIndexFROM sys.partitions PARINNER JOIN s
阅读全文
摘要：USE master;gocreate DATABASE ncdbON PRIMARY( NAME = N'ncdb', FILENAME = N'D:\sqldata\ncdb\ncdb.mdf' , SIZE = 1000 , FILEGROWTH = 200, MAXSIZE = 10000)
阅读全文
摘要：当前消耗cpu较高的前十条语句 SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) 'Elaps M', SU
阅读全文
摘要：SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.ba
阅读全文
摘要：-- sql server 2012 1、查询数据库名SELECT DB_NAME(database_id) AS [Database Name],[Name] AS [Logical Name],[Physical_Name] AS [Physical Name],((size * 8) / 10
阅读全文
摘要：CREATE PROCEDURE [dbo].[sp_who_lock] AS BEGIN DECLARE @spid INT , @bl INT , @intTransactionCountOnEntry INT , @intRowcount INT , @intCountProperties I
阅读全文
