Home Database Mysql Tutorial SQLServer常用代码段

SQLServer常用代码段

Jun 07, 2016 pm 02:57 PM
sqlserver code several Commonly used use

几个常用的SQLServer语句 无 ------------------------------------------------------------------------GO--查询库中表的行数 打印大于0行的表名和行数DECLARE @TableName VARCHAR(128)Declare PostCur Cursor For SELECT name FROM sys.tables Open PostCu

几个常用的SQLServer语句
------------------------------------------------------------------------
GO
--查询库中表的行数 打印大于0行的表名和行数
DECLARE @TableName VARCHAR(128)
Declare PostCur Cursor For   
SELECT name FROM sys.tables  
Open PostCur  
Fetch next From PostCur Into @TableName  
While @@fetch_status=0  
	Begin  	
	DECLARE @ct INT
	SET @ct=0
	DECLARE @strSQL NVARCHAR(500)
	SET @strSQL='Select @ct=Count(1) From '+@TableName
	exec sp_executesql @strSQL,N'@ct int output',@ct OUTPUT
	IF(@ct>0)
	BEGIN
		print '表名 '+@TableName
		PRINT '数据条数 '+Convert(varchar(32),@ct)
	END
	Fetch next From PostCur Into @TableName    
	End  
Close PostCur  
Deallocate PostCur

----------------------------------------
--查询数据库总各表数据量
GO
CREATE TABLE #tbles(ID int IDENTITY(1,1),TableName varchar(128)
	,IsHandle bit DEFAULT('False')
	,TableRowCount int DEFAULT(0))
INSERT INTO #tbles(TableName)
SELECT name FROM sys.tables
DECLARE @TableName varchar(128)
DECLARE @ID int
SET @ID=0
SELECT TOP 1 @ID=ID,@TableName=TableName FROM #tbles WHERE IsHandle='False'
WHILE(@ID!=0)
BEGIN
	DECLARE @isexists bit
	DECLARE @RowCount int
	DECLARE @SqlStr nvarchar(1000)
	SET @SqlStr='SELECT @RowCount=Count(1) FROM '+@TableName
	exec sp_executesql @SqlStr
	,N'@RowCount int output',@RowCount OUTPUT
	UPDATE #tbles SET IsHandle='True',TableRowCount=@RowCount WHERE TableName=@TableName
	Print @RowCount
	PRINT @TableName	
	SET @ID=0
	SET @RowCount=0
	SET @SqlStr=''
	SELECT TOP 1 @ID=ID,@TableName=TableName FROM #tbles WHERE IsHandle='False'
END
SELECT * FROM #tbles
Drop Table #tbles


------------------------------------------------------------------------
---查询存储过程返回结果
GO
create proc getdata2
as
select 1 as r1,2 as r2

GO
create table #temp(r1 int,r2 int)
insert into #temp exec getdata2
select * From #temp
drop table #temp


------------------------------------------------------------------------
----查询生成拼接字符串
Go
create table #temp1(c1 int)
insert into #temp1(c1)Values(1)
insert into #temp1(c1)Values(2)
insert into #temp1(c1)Values(3)
insert into #temp1(c1)Values(4)
GO
Select c1 From #temp1 for xml path('')
Select c1 as [data()] From #temp1 for xml path('')
Select Convert(varchar(10),c1) +',' From #temp1 for xml path('')
declare @str varchar(max)
select @str=(Select Convert(varchar(10),c1) +',' From #temp1 for xml path(''))
select @str


------------------------------------------------------------------------
--获取随机字符串
GO
CREATE VIEW [dbo].[V_RAND]
AS
SELECT RAND1 = CONVERT(INT,RAND()*26),RAND2   =   RAND()*2

GO
Create   FUNCTION   [dbo].[f_GetRandStr](@LEN   INT,@FLAG   INT) 
RETURNS   NVARCHAR(100) 
AS 
--@LEN   输出字符的长度 
--@FLAG   返回值包含字符   1:大写字母      2:小写字母      3:大小写字母混合 
BEGIN 
       DECLARE   @SQL   NVARCHAR(100),@RAND   INT 
       SELECT   @SQL   =    '' 
       IF   @LEN>100 
             SET @LEN   =   100 
  
       WHILE   @LEN>0    
       BEGIN 
             SELECT @RAND   =   RAND1 +(CASE   @FLAG   WHEN   1   THEN   65   WHEN   2   THEN   97 
                   ELSE(CASE WHEN RAND2 > 1 THEN   97   ELSE   65   END)   END) 
             FROM   V_RAND 
  
             SELECT   @SQL=@SQL + CHAR(@RAND),@LEN  = @LEN - 1 
       END 
  
       RETURN   @SQL 
END
GO
Select dbo.f_GetRandStr(30,3)

----------------------------------------
--简单的传参输出
GO
declare @i3 int
exec sp_executesql N'Select @i3=@i1+@i2',N'@i1 int,@i2 int,@i3 int output',1,22,@i3 output
Select @i3
Copy after login
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to solve the problem that the object named already exists in the sqlserver database How to solve the problem that the object named already exists in the sqlserver database Apr 05, 2024 pm 09:42 PM

For objects with the same name that already exist in the SQL Server database, the following steps need to be taken: Confirm the object type (table, view, stored procedure). IF NOT EXISTS can be used to skip creation if the object is empty. If the object has data, use a different name or modify the structure. Use DROP to delete existing objects (use caution, backup recommended). Check for schema changes to make sure there are no references to deleted or renamed objects.

How to import mdf file into sqlserver How to import mdf file into sqlserver Apr 08, 2024 am 11:41 AM

The import steps are as follows: Copy the MDF file to SQL Server's data directory (usually C:\Program Files\Microsoft SQL Server\MSSQL\DATA). In SQL Server Management Studio (SSMS), open the database and select Attach. Click the Add button and select the MDF file. Confirm the database name and click the OK button.

What to do if the sqlserver service cannot be started What to do if the sqlserver service cannot be started Apr 05, 2024 pm 10:00 PM

When the SQL Server service fails to start, here are some steps to resolve: Check the error log to determine the root cause. Make sure the service account has permission to start the service. Check whether dependency services are running. Disable antivirus software. Repair SQL Server installation. If the repair does not work, reinstall SQL Server.

How to check sqlserver port number How to check sqlserver port number Apr 05, 2024 pm 09:57 PM

To view the SQL Server port number: Open SSMS and connect to the server. Find the server name in Object Explorer, right-click it and select Properties. In the Connection tab, view the TCP Port field.

How to recover accidentally deleted database in sqlserver How to recover accidentally deleted database in sqlserver Apr 05, 2024 pm 10:39 PM

If you accidentally delete a SQL Server database, you can take the following steps to recover: stop database activity; back up log files; check database logs; recovery options: restore from backup; restore from transaction log; use DBCC CHECKDB; use third-party tools. Please back up your database regularly and enable transaction logging to prevent data loss.

Where is the sqlserver database? Where is the sqlserver database? Apr 05, 2024 pm 08:21 PM

SQL Server database files are usually stored in the following default location: Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data The database file location can be customized by modifying the database file path setting.

How to delete sqlserver if the installation fails? How to delete sqlserver if the installation fails? Apr 05, 2024 pm 11:27 PM

If the SQL Server installation fails, you can clean it up by following these steps: Uninstall SQL Server Delete registry keys Delete files and folders Restart the computer

How to change sqlserver English installation to Chinese How to change sqlserver English installation to Chinese Apr 05, 2024 pm 10:21 PM

SQL Server English installation can be changed to Chinese by following the following steps: download the corresponding language pack; stop the SQL Server service; install the language pack; change the instance language; change the user interface language; restart the application.

See all articles