Collect several sections of SQL Server statements and stored procedures

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