Collect several sections of SQL Server statements and stored procedures
Last Update:2017-02-28
Source: Internet
Author: User
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