Collect several sections of SQL Server statements and stored procedures

Source: Internet
Author: User
Tags count end insert join sql query table name
Server| Stored Procedure | statement
Collect several sections of SQL Server statements and stored procedures



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

--Lists all SQL SERVER tables, field names, primary keys, types, lengths, decimal places, and other information

--run in Query Analyzer, you can generate a table, export to Excel

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

SELECT

(case when A.colorder=1 then D.name else "end") Table name,

A.colorder field ordinal number,

A.name Field Name,

(Case when ColumnProperty (A.id,a.name, ' isidentity ') =1 then ' √ ' Else ' "End ') identifies,

(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 ' primary key,

B.name type,

A.length occupies a number of bytes,

ColumnProperty (a.id,a.name, ' PRECISION ') as length,

IsNull (ColumnProperty (a.id,a.name, ' Scale '), 0) as decimal digits,

(case when a.isnullable=1 then ' √ ' else ') allows NULL,

IsNull (E.text, ') default value,

IsNull (G.[value], ') as field description



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

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













Lists all SQL SERVER tables, field definitions, types, lengths, values, etc.

and exported to Excel

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

--Export all user tables definition and one sample value

--Jan-13-2003,dr.zhang

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

Running in Query Analyzer:

SET ansi_nulls off

Go

SET NOCOUNT on

Go



SET LANGUAGE ' Simplified Chinese '

Go

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



SELECT d.name tablename,a.name fieldname,b.name typename,a.length length,a.isnullable 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 the 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 ',

' 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 (' + @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)) = ' +conv ert (nvarchar (5), @maxlen) + ') '

EXEC sp_executesql @sql, N ' @sample varchar output ', @sample output

--for quickly

--set @sql =n ' SET @sample =convert (varchar), (SELECT top 1 ' + @fld + ' from ' +

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

PRINT @sql

Print @sample

Print @tbl

EXEC sp_executesql @sql, N ' @sample nvarchar output ', @sample output

INSERT into #tc SELECT *,ltrim (ISNULL (@maxlen, 0)) as maxlenused,

CONVERT (nchar), 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

Go



SET ANSI_NULLS on

Go

SET NOCOUNT off

Go

Select COUNT (*) from #t

DROP TABLE #t

Go



Select COUNT (*)-1 from #tc



SELECT * into # #tx to #tc ORDER by TableName

DROP TABLE #tc



--select * FROM # #tx



Declare @db nvarchar, @sql nvarchar (3000)

Set @db =db_name ()

--Please modify the username and password to export to Excel

Set @sql = ' exec master.dbo.xp_cmdshell ' bcp ... dbo.# #tx out C:\ ' + @db + ' _exp.xls-w-c936-usa-psa '

Print @sql

EXEC (@sql)

Go

DROP TABLE # #tx

Go







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

--A stored procedure that generates INSERT statements based on data in a table

--establishes the stored procedure, executes the Spgeninsertsql table name

--Thank Playyuer

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

CREATE proc Spgeninsertsql (@tablename varchar (256))



As

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 ' + ' ' ' + ' + ' ' + ' ' + ' ' + ' + ' as varchar ' + ' + ' '

When xtype in (167)

Then ' case when ' + name + ' is null then ' null ' else ' + ' ' + ' ' + ' + ' replace (' + name+ ', ' ' ', ' ', ' ', ' ', ' ' ', ' ' ' + ' + ' "" ' + ' end '

When xtype in (231)

Then ' case if ' + 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+ ', ' ' ', ', ' ', ' ', ' ' Char (' + cast (length as varchar) + ')) + ' "', ' + ' end '

When xtype in (239)

Then ' case when ' + name + ' is null then ' null ' else ' + ' ' N ' ' + ' ' + ' + ' cast (replace (' + name+ ', ' ' ', ', ' ', ' ', ' ' 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



Go







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

--A stored procedure that generates INSERT statements based on data in a table

--establishes the stored procedure, executes the Proc_insert table name

--Thank Sky_blue

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



CREATE proc Proc_insert (@tablename varchar (256))

As

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 (4), varchar ength*2+2) + '), ' +a.name + ') ' + ' end '

When A.xtype =104 then ' case when ' +a.name+ ' are 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), ' +a.name + ', 121 ) ' + ' + ' ' ', ' ' + ' end '

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

When A.xtype =62 then ' case when ' +a.name+ ' are null then ' ' null ' else ' + ' convert (varchar (), ' +a.name + ', 2) ' + ' end '

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

When A.xtype =60 then ' case when ' +a.name+ ' are null then ' ' null ' else ' + ' convert (varchar), ' +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 (4), varchar) + '), ' +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+ ' are null then ' ' null ' else ' + ' convert (varchar (), ' +a.name + ', 2) ' + ' end '

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

When A.xtype =52 then ' case when ' +a.name+ ' are null then ' ' null ' else ' + ' convert (varchar (), ' +a.name + ') ' + ' end '

When A.xtype =122 then ' case when ' +a.name+ ' are null then ' ' null ' else ' + ' convert (varchar), ' +a.name + ') ' + ' end '

When A.xtype =48 then ' case when ' +a.name+ ' are 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 (4), varchar ength*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 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

Go


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.