建库建表语句create database database1 --建库 on primary ( nameServerWuliao, filenameD:\MSSQL\DATA\ServerWuliao.mdf, size10MB, maxsize50MB, filegrowth25% ) log on ( nameServerWuliao, filenameD:\MSSQL\DATA\ServerWuliao_log.ldf, size10MB, maxsize50MB, filegrowth2MB ) --建表语句 USE [mesdb] GO /****** Object: Table [dbo].[inspection_standard] Script Date: 2025/5/7 16:08:53 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[table1]( [id] [int] IDENTITY(1,1) NOT NULL, [parentID] [int] NULL, [sourceID] [int] NULL, [ver] [varchar](50) NULL, [forms] [varchar](50) NULL, [formno] [varchar](50) NULL, [mark] [varchar](50) NULL, [Operator] [varchar](50) NULL, [OperDate] [datetime] NULL, [items] [int] NULL, [UUID] [varchar](50) NULL, ) ON [PRIMARY] GO按照旧表结构建新表--第一种方法 SELECT * INTO NewTable FROM OldTable WHERE 1 0 --NewTable新表 OldTable旧表 --第二种方法 右键旧表 - 编写表脚本为 - Create到 - 新查询编辑器窗口不同ip复制表1到表2--分行执行 EXEC sp_configure show advanced options,1; RECONFIGURE; EXEC sp_configure Ad Hoc Distributed Queries,1; RECONFIGURE; --复制结构和内容 SELECT * INTO mesdb.dbo.table2 FROM opendatasource( SQLOLEDB, Data Source源数据库ip;User IDsa;Password密码 ).mesdb.dbo.table1; --复制结构 SELECT * INTO mesdb.dbo.table2 FROM opendatasource( SQLOLEDB, Data Source源数据库ip;User IDsa;Password密码 ).mesdb.dbo.table1 WHERE 12; --分行执行 EXEC sp_configure Ad Hoc Distributed Queries,0; RECONFIGURE; EXEC sp_configure show advanced options,0; RECONFIGURE;查询表全部信息EXEC sp_help table1查询表字段信息SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name table1;查询数据库存储位置SELECT name, physical_name FROM sys.master_files WHERE database_id DB_ID(dataBase1); --查询数据库存储位置查询数据库当前大小SELECT DB_NAME(database_id) AS DatabaseName, CAST(SUM(size) * 8 / 1024.0 AS DECIMAL(10, 2)) AS DatabaseSizeMB FROM sys.master_files WHERE database_id DB_ID(DataBase1) GROUP BY database_id;查询最大内存SELECT name AS FileName, type_desc AS FileType, CAST(size * 8 / 1024.0 AS DECIMAL(10, 2)) AS FileSizeMB, max_size AS MaxSize FROM sys.database_files WHERE name DataBase1;不能为表中的标识列插入显式值?-- 开启对特定表的标识插入权限 SET IDENTITY_INSERT table1 ON; -- 插入数据此时可以为标识列指定一个值 insert into table1(id... ) values(1...) -- 插入完成后关闭权限 SET IDENTITY_INSERT table1 OFF;回滚BEGIN TRANSACTION update 语句 ROLLBACK TRANSACTION;查询数据库中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) AS Elaps M, SUBSTRING(st.TEXT, (r.statement_start_offset / 2) 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) 1) AS statement_text, COALESCE(QUOTENAME(DB_NAME(st.dbid)) N. QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) N. QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), ) AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id ! SPID ORDER BY r.cpu_time DESC;查询SQLSERVER版本、补丁和版本级别SELECT SERVERPROPERTY(ProductVersion) AS ProductVersion, SERVERPROPERTY(ProductLevel) AS ProductLevel, SERVERPROPERTY(Edition) AS Edition;配置远程服务器连接-- 删除现有的链接服务器 EXEC sp_dropserver server RemoteServer, droplogins droplogins; -- 重新创建链接服务器 EXEC sp_addlinkedserver server RemoteServer, srvproduct , provider SQLNCLI11, datasrc 数据库ip地址; -- 重新配置登录信息 EXEC sp_addlinkedsrvlogin rmtsrvname RemoteServer, useself false, locallogin NULL, rmtuser 账号, rmtpassword 密码;还原数据库RESTORE DATABASE [YourDatabaseName] FROM DISK C:\Backup\YourDatabaseName.bak WITH REPLACE;
SQLSERVER语句记录
建库建表语句create database database1 --建库 on primary ( nameServerWuliao, filenameD:\MSSQL\DATA\ServerWuliao.mdf, size10MB, maxsize50MB, filegrowth25% ) log on ( nameServerWuliao, filenameD:\MSSQL\DATA\ServerWuliao_log.ldf, size10MB, maxsize50MB, filegrowth2MB ) --建表语句 USE [mesdb] GO /****** Object: Table [dbo].[inspection_standard] Script Date: 2025/5/7 16:08:53 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[table1]( [id] [int] IDENTITY(1,1) NOT NULL, [parentID] [int] NULL, [sourceID] [int] NULL, [ver] [varchar](50) NULL, [forms] [varchar](50) NULL, [formno] [varchar](50) NULL, [mark] [varchar](50) NULL, [Operator] [varchar](50) NULL, [OperDate] [datetime] NULL, [items] [int] NULL, [UUID] [varchar](50) NULL, ) ON [PRIMARY] GO按照旧表结构建新表--第一种方法 SELECT * INTO NewTable FROM OldTable WHERE 1 0 --NewTable新表 OldTable旧表 --第二种方法 右键旧表 - 编写表脚本为 - Create到 - 新查询编辑器窗口不同ip复制表1到表2--分行执行 EXEC sp_configure show advanced options,1; RECONFIGURE; EXEC sp_configure Ad Hoc Distributed Queries,1; RECONFIGURE; --复制结构和内容 SELECT * INTO mesdb.dbo.table2 FROM opendatasource( SQLOLEDB, Data Source源数据库ip;User IDsa;Password密码 ).mesdb.dbo.table1; --复制结构 SELECT * INTO mesdb.dbo.table2 FROM opendatasource( SQLOLEDB, Data Source源数据库ip;User IDsa;Password密码 ).mesdb.dbo.table1 WHERE 12; --分行执行 EXEC sp_configure Ad Hoc Distributed Queries,0; RECONFIGURE; EXEC sp_configure show advanced options,0; RECONFIGURE;查询表全部信息EXEC sp_help table1查询表字段信息SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name table1;查询数据库存储位置SELECT name, physical_name FROM sys.master_files WHERE database_id DB_ID(dataBase1); --查询数据库存储位置查询数据库当前大小SELECT DB_NAME(database_id) AS DatabaseName, CAST(SUM(size) * 8 / 1024.0 AS DECIMAL(10, 2)) AS DatabaseSizeMB FROM sys.master_files WHERE database_id DB_ID(DataBase1) GROUP BY database_id;查询最大内存SELECT name AS FileName, type_desc AS FileType, CAST(size * 8 / 1024.0 AS DECIMAL(10, 2)) AS FileSizeMB, max_size AS MaxSize FROM sys.database_files WHERE name DataBase1;不能为表中的标识列插入显式值?-- 开启对特定表的标识插入权限 SET IDENTITY_INSERT table1 ON; -- 插入数据此时可以为标识列指定一个值 insert into table1(id... ) values(1...) -- 插入完成后关闭权限 SET IDENTITY_INSERT table1 OFF;回滚BEGIN TRANSACTION update 语句 ROLLBACK TRANSACTION;查询数据库中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) AS Elaps M, SUBSTRING(st.TEXT, (r.statement_start_offset / 2) 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) 1) AS statement_text, COALESCE(QUOTENAME(DB_NAME(st.dbid)) N. QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) N. QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), ) AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id ! SPID ORDER BY r.cpu_time DESC;查询SQLSERVER版本、补丁和版本级别SELECT SERVERPROPERTY(ProductVersion) AS ProductVersion, SERVERPROPERTY(ProductLevel) AS ProductLevel, SERVERPROPERTY(Edition) AS Edition;配置远程服务器连接-- 删除现有的链接服务器 EXEC sp_dropserver server RemoteServer, droplogins droplogins; -- 重新创建链接服务器 EXEC sp_addlinkedserver server RemoteServer, srvproduct , provider SQLNCLI11, datasrc 数据库ip地址; -- 重新配置登录信息 EXEC sp_addlinkedsrvlogin rmtsrvname RemoteServer, useself false, locallogin NULL, rmtuser 账号, rmtpassword 密码;还原数据库RESTORE DATABASE [YourDatabaseName] FROM DISK C:\Backup\YourDatabaseName.bak WITH REPLACE;
