Add SQL Server statements and stored procedures to Favorites (csdn)

Add SQL Server statements and stored procedures to favorites

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

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

-- Run the query analyzer to generate a table and export it to excel.

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

Select

(Case when a. colorder = 1 then D. Name else ''end) Table Name,

A. Serial number of the colorder field,

A. Name field name,

(Case when columnproperty (A. ID, A. Name, 'isidentity ') = 1 then' √ 'else' end) ID,

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

B. Name type,

A. Length occupies the number of bytes,

Columnproperty (A. ID, A. Name, 'precision ') as length,

Isnull (columnproperty (A. ID, A. Name, 'Scale'), 0) as decimal places,

(Case when a. isnullable = 1 then '√ 'else'' End) Allow null,

Isnull (E. Text, '') default value,

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

 

From syscolumns a left join policypes 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

Bytes -------------------------------------------------------------------------------------------------

 

 

 

 

 

 

Lists All SQL Server tables, field definitions, types, lengths, and values.

And export it to excel

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

-- Export all user tables definition and one sample value

-- Jan-13-2003, dr. Zhang

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

Run in the query Analyzer:

Set ansi_nulls off

Go

Set nocount on

Go

 

Set language 'simplified China'

Go

Declare @ TBL nvarchar (200), @ brief 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, policypes 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, @ override

While (@ fetch_status <>-1) --- failes

Begin

If (@ fetch_status <>-2) -- Missing

Begin

Set @ SQL = n' set @ maxlen = (select max (LEN (cast ('+ @ alias +' 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 ('+ @ alias +' As nvarchar) from '+ @ TBL +' where Len (cast ('+ @ alias +' 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' + @ resolve + '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 = @ resolve

End

Fetch next from read_cursor into @ TBL, @ override

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 from # TC order by tablename

Drop table # TC

 

-- Select * from # TX

 

Declare @ dB nvarchar (60), @ SQL nvarchar (3000)

Set @ DB = db_name ()

-- Please modify the user name and password and export it to excel

Set @ SQL = 'exec master. DBO. xp_mongoshell ''bcp... DBO. # TX OUT C:/'{@db='_exp.xls-w-c936-USA-PSA '''

Print @ SQL

Exec (@ SQL)

Go

Drop table # TX

Go

 

 

 

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

-- Storage process for generating insert statements based on table data

-- Create a stored procedure and execute the spgeninsertsql table name

-- Thanks to 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' + ''' +' + '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

 

Go

 

 

 

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

-- Storage process for generating insert statements based on table data

-- Create a stored procedure and execute the proc_insert table name

-- Thanks to 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. xtype = 173 then 'case when' +. name + 'is null then' 'null' 'else' + 'convert (varchar ('+ convert (varchar (4),. length * 2 + 2) + '),' +. name + ')' + 'end'

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

When. xtype = 175 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'Replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'

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

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

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

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

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

When. xtype = 239 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'Replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'

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

When. xtype = 231 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'Replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'

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

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

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

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

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

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

When. xtype = 167 then 'case when' +. name + 'is null then' 'null' 'else' + ''' + 'Replace (' +. name + ','''''''','''''''''''') '+ ''' + 'end'

Else '''null '''

End as COL, A. colid, A. Name

From syscolumns A where. id = object_id (@ tablename) and. xtype <> 189 and. xtype <> 34 and. xtype <> 35 and. 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