收藏几段SQL Server语句和存储过程-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

收藏几段SQL Server语句和存储过程

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:04 PM

server sql Several storage collect statement process

收藏几段SQL Server 语句和存储过程 (标准化越来越近了) :namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> -- ====================================================== --列出sql server (WINDOWS平台上强大的数据库平台) 所

收藏几段SQL Server语句和存储过程(标准化越来越近了):namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

-- ======================================================

--列出sql server(WINDOWS平台上强大的数据库平台) 所有表，字段名，主键，类型，长度，小数位数等信息

--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中

-- ======================================================

SELECT

　　　 (case when a.colorder=1 then d.name else '' end)表名,

　　　 a.colorder 字段序号,

　　　 a.name 字段名,

　　　 (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,

　　　 (case when (SELECT count(*)

　　　 FROM sysobjects

　　　 WHERE (name in

　　　　　　　(SELECT name

　　　　　　　 FROM sysindexes

　　　　　　 WHERE (id = a.id) AND (indid in

　　　　　　　　　　　 (SELECT indid

　　　　　　　　　　　 FROM sysindexkeys

　　　　　　　　　　　WHERE (id = a.id) AND (colid in

　　　　　　　　　　　　　　　　(SELECT colid

　　　　　　　　　　　　　　　 FROM syscolumns

　　　　　　　　　　　　　　　　 WHERE (id = a.id) AND (name = a.name))))))) AND

　　　　　　(xtype = 'PK'))>0 then '√' else '' end) 主键,

　　　 b.name 类型,

　　　 a.length 占用字节数,

　　　 COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,

　　　 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,

　　　 (case when a.isnullable=1 then '√'else '' end) 允许空,

　　　 isnull(e.text,'') 默认值,

　　　 isnull(g.[value],'') AS 字段说明　

FROM syscolumns a left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='U' and d.name'dtproperties'

left join syscomments e

on a.cdefault=e.id

left join sysproperties g

on a.id=g.id AND a.colid = g.smallid

order by a.id,a.colorder

-------------------------------------------------------------------------------------------------

列出sql server(WINDOWS平台上强大的数据库平台) 所有表、字段定义，类型，长度，一个值等信息

并导出到Excel 中

-- ======================================================

-- Export all user tables definition and one sample value

-- jan-13-2003,Dr.Zhang

-- ======================================================

在查询分析器里运行：

SET ANSI_NULLS OFF

SET NOCOUNT ON

SET LANGUAGE 'Simplified Chinese'

DECLARE @tbl nvarchar(200),@fld nvarchar(200),@sql nvarchar(4000),@maxlen int,@sample nvarchar(40)

SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t

FROM syscolumns a, systypes b,sysobjects d

WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U'

DECLARE read_cursor CURSOR

FOR SELECT TableName,FieldName FROM #t

SELECT TOP 1 '_TableName　　　　　　　　　　 ' TableName,

　　　　　 'FieldName　　　　　　　　　　 ' FieldName,'TypeName　　　　　　 ' TypeName,

　　　　　 'Length' Length,'IS_NULL' IS_NULL,

　　　　　 'MaxLenUsed' AS MaxLenUsed,'Sample Value　　　　 ' Sample,

　　　　　　 'Comment　 ' Comment INTO #tc FROM #t

OPEN read_cursor

FETCH NEXT FROM read_cursor INTO @tbl,@fld

WHILE (@@fetch_status -1) --- failes

BEGIN

　　　 IF (@@fetch_status -2) -- Missing

　　　 BEGIN

　　　　　　 SET @sql=N'SET @maxlen=(SELECT max(len(cast('+@fld+' as nvarchar))) FROM '+@tbl+')'

　　　　　　 --PRINT @sql

　　　　　　 EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT

　　　　　　 --print @maxlen

　　　　　　 SET @sql=N'SET @sample=(SELECT TOP 1 cast('+@fld+' as nvarchar) FROM '+@tbl+' WHERE len(cast('+@fld+' as nvarchar))='+convert(nvarchar(5),@maxlen)+')'

　　　　　　 EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT

　　　　　　 --for quickly　

　　　　　　 --SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+

　　　　　　　　　　 --@tbl+' order by 1 desc ))'

　　　　　　 PRINT @sql

　　　　　　 print @sample

　　　　　　 print @tbl

　　　　　　 EXEC SP_EXECUTESQL @sql,N'@sample nvarchar(30) OUTPUT',@sample OUTPUT

　　　　　　 INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,

　　　　　　　　　　 convert(nchar(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld

　　　 END

　　　 FETCH NEXT FROM read_cursor INTO @tbl,@fld

END

CLOSE read_cursor

DEALLOCATE read_cursor

SET ANSI_NULLS ON

SET NOCOUNT OFF

select count(*) from #t

DROP TABLE #t

select count(*)-1 from #tc

select * into ##tx from #tc order by tablename

DROP TABLE #tc

--select * from ##tx

declare @db nvarchar(60),@sql nvarchar(3000)

set @db=db_name()

--请修改用户名和口令导出到Excel 中

set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -w -C936 -(标准化越来越近了):namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />Usa -Psa '''

print @sql

exec(@sql)

DROP TABLE ##tx

-- ======================================================

--根据表中数据生成insert语句的存储过程

--建立存储过程，执行 spGenInsertSQL 表名

--感谢playyuer

-- ======================================================

CREATE　 proc spGenInsertSQL (@tablename varchar(256))

begin

declare @sql varchar(8000)

declare @sqlValues varchar(8000)

set @sql =' ('

set @sqlValues = 'values (''+'

select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'

　 from

　　　 (select case

　　　　　　　　 when xtype in (48,52,56,59,60,62,104,106,108,122,127)　　　　　　　　　　　　　　　

　　　　　　　　　　　then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'

　　　　　　　　 when xtype in (58,61)

　　　　　　　　　　　 then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'

　　　　　　　 when xtype in (167)

　　　　　　　　　　　 then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

　　　　　　　　 when xtype in (231)

　　　　　　　　　　　 then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

　　　　　　　　 when xtype in (175)

　　　　　　　　　　　 then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'

　　　　　　　　 when xtype in (239)

　　　　　　　　　　　 then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'

　　　　　　　　 else '''NULL'''

　　　　　　　 end as Cols,name

　　　　　 from syscolumns

　　　　 where id = object_id(@tablename)

　　　 ) T

set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename

--print @sql

exec (@sql)

end

-- ======================================================

--根据表中数据生成insert语句的存储过程

--建立存储过程，执行 proc_insert 表名

--感谢Sky_blue

-- ======================================================

CREATE proc proc_insert (@tablename varchar(256))

begin

　　　 set nocount on

　　　 declare @sqlstr varchar(4000)

　　　 declare @sqlstr1 varchar(4000)

　　　 declare @sqlstr2 varchar(4000)

　　　 select @sqlstr='select ''insert '+@tablename

　　　 select @sqlstr1=''

　　　 select @sqlstr2=' ('

　　　 select @sqlstr1= ' values ( ''+'

　　　 select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case

--　　 when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'

　　　 when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'

　　　 when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

　　　 when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'

　　　 when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'

　　　 when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'

　　　 when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'

　　　 when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'

　　　 when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

　　　 when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'

　　　 when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

　　　 when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'

　　　 when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'

　　　 when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'

　　　 when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'

　　　 when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'

--　　 when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'

　　　 when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

　　　 else '''NULL'''

　　　 end as col,a.colid,a.name

　　　 from syscolumns a where a.id = object_id(@tablename) and a.xtype 189 and a.xtype 34 and a.xtype 35 and a.xtype 36

　　　 )t order by colid

　　　 select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename

-- print @sqlstr

　　　 exec( @sqlstr)

　　　 set nocount off

end

1 2 3 4 5 6 下一页

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

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Repo: How To Revive Teammates

1 months ago By 尊渡假赌尊渡假赌尊渡假赌

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hello Kitty Island Adventure: How To Get Giant Seeds

1 months ago By 尊渡假赌尊渡假赌尊渡假赌

How Long Does It Take To Beat Split Fiction?

4 weeks ago By DDD

R.E.P.O. Save File Location: Where Is It & How to Protect It?

4 weeks ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Where is the login entrance for gmail email?

7366

Java Tutorial

1628

CakePHP Tutorial

1354

Laravel Tutorial

1266

PHP Tutorial

1214

Related knowledge

What is the difference between HQL and SQL in Hibernate framework? Apr 17, 2024 pm 02:57 PM

HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).

Usage of division operation in Oracle SQL Mar 10, 2024 pm 03:06 PM

"Usage of Division Operation in OracleSQL" In OracleSQL, division operation is one of the common mathematical operations. During data query and processing, division operations can help us calculate the ratio between fields or derive the logical relationship between specific values. This article will introduce the usage of division operation in OracleSQL and provide specific code examples. 1. Two ways of division operations in OracleSQL In OracleSQL, division operations can be performed in two different ways.

Comparison and differences of SQL syntax between Oracle and DB2 Mar 11, 2024 pm 12:09 PM

Oracle and DB2 are two commonly used relational database management systems, each of which has its own unique SQL syntax and characteristics. This article will compare and differ between the SQL syntax of Oracle and DB2, and provide specific code examples. Database connection In Oracle, use the following statement to connect to the database: CONNECTusername/password@database. In DB2, the statement to connect to the database is as follows: CONNECTTOdataba

What does the identity attribute in SQL mean? Feb 19, 2024 am 11:24 AM

What is Identity in SQL? Specific code examples are needed. In SQL, Identity is a special data type used to generate auto-incrementing numbers. It is often used to uniquely identify each row of data in a table. The Identity column is often used in conjunction with the primary key column to ensure that each record has a unique identifier. This article will detail how to use Identity and some practical code examples. The basic way to use Identity is to use Identit when creating a table.

Huawei will launch innovative MED storage products next year: rack capacity exceeds 10 PB and power consumption is less than 2 kW Mar 07, 2024 pm 10:43 PM

This website reported on March 7 that Dr. Zhou Yuefeng, President of Huawei's Data Storage Product Line, recently attended the MWC2024 conference and specifically demonstrated the new generation OceanStorArctic magnetoelectric storage solution designed for warm data (WarmData) and cold data (ColdData). Zhou Yuefeng, President of Huawei's data storage product line, released a series of innovative solutions. Image source: Huawei's official press release attached to this site is as follows: The cost of this solution is 20% lower than that of magnetic tape, and its power consumption is 90% lower than that of hard disks. According to foreign technology media blocksandfiles, a Huawei spokesperson also revealed information about the magnetoelectric storage solution: Huawei's magnetoelectronic disk (MED) is a major innovation in magnetic storage media. First generation ME

Detailed explanation of the Set tag function in MyBatis dynamic SQL tags Feb 26, 2024 pm 07:48 PM

Interpretation of MyBatis dynamic SQL tags: Detailed explanation of Set tag usage MyBatis is an excellent persistence layer framework. It provides a wealth of dynamic SQL tags and can flexibly construct database operation statements. Among them, the Set tag is used to generate the SET clause in the UPDATE statement, which is very commonly used in update operations. This article will explain in detail the usage of the Set tag in MyBatis and demonstrate its functionality through specific code examples. What is Set tag Set tag is used in MyBati

How to add soda music songs to Douyin Feb 23, 2024 pm 04:52 PM

How to add soda music songs to Douyin? Songs in the soda music APP can be synchronized to the Douyin platform, but most friends don’t know how to add soda music songs to Douyin. Next, the editor will guide users Here is a tutorial on how to add soda music songs to Douyin. Interested users can come and take a look! Tutorial on how to use soda music. How to add soda music songs to Douyin. 1. First open the soda music APP and click the [Music] icon at the bottom of the main page to enter the page; 2. Then on the song playback page, click [as shown by the arrow in the picture below] Share] button; 3. Finally, in the function bar expanded below, select the [Douyin] icon to share to the corresponding platform.

How to install, uninstall, and reset Windows server backup Mar 06, 2024 am 10:37 AM

WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows

See all articles