Collection Several SQL Server statements and stored procedures
- ================================================================================================================================================================== ======
- List the SQL Server all tables, field names, primary keys, types, length, number of small digits, etc.
- Run in the query analyzer, you can generate a table, export to Excel
- ================================================================================================================================================================== ======
SELECT
(CASE WHEN A.COLORDER = 1 THEN D.NAME ELSE '' END) Type,
A.ColORDER field serial number,
A.Name field name,
(Case When ColumnProperty (A.ID, A.Name, 'isidentity') = 1 Then '√'lse' '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)))))))))))
(Xtype = 'pk'))> 0 THEN '√' Else '' end) Primary key,
B.Name type,
A.Length occupies bytes,
ColumnProperty (A.ID, A.Name, 'Precision') AS length,
ISNULL (A.ID, A.NAME, 'Scale'), 0) AS count number,
(Case When A.isnullable = 1 Then '√'ELSE' 'End) Allow empty,
ISNULL (E.TEXT, '') default value,
ISNULL (g. [value], '') AS field description
From syscolumns a left join systemypes 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 eon a.cdefault = E.ID
Left Join Sysproperties G
ON A.ID = g.id and a.colid = g.smallid
Order by a.id, a.colorder
-------------------------------------------------- -----------------------------------------------
List the SQL Server all tables, field definitions, types, length, one value, etc.
And export 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 Chinese'
Go
Declare @tbl nvarchar (200), @ fld nvarchar (200), @ SQL NVARCHAR (4000), @ Maxlen Int, @seple nvarchar (40)
Select D.Name Tablename, A.Name Fieldname, B.Name Typename, A.Length Length, A.isnullable IS_NULL INTO #T
From syscolumns a, systempes 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 #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)) "" ""
--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', @seple 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', @seple 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
Go
SET ANSI_NULLS ON
Go
Set nocount off
Go
SELECT Count (*) from #t #t
DROP TABLE #T
Go
SELECT Count (*) - 1 from #TC
Select * inTo ## TX from #tc Order by Tablenamedrop Table #TC
--Select * from ## TX
Declare @db nvarchar (60), @ 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
- ================================================================================================================================================================== ======
- The stored procedure of generating an INSERT statement according to the data in the table
- Establish a stored procedure, execute 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 TEN' 'NULL' 'ELSE' 'CAST (' Name 'As Varchar)' 'end'
When Xtype in (58, 61)
THEN 'CASE WHEN' NAME 'IS NULL THEN' '' '' 'ELSE' '' '' '' '' ' ' CAST (' Name ' As Varchar) ' ' '' '' '' ' ' end'when xtype in (167)
THEN 'CASE WHEN' NAME 'is NULL THEN' '' '' '' ELSE ' ' '' '' '' ' ' Replace (' Name ', '' '' ',' '' '' '' '' '' ' ' '' '' '' ' ' End '
WHEN Xtype in (231)
THEN 'CASE WHEN' NAME 'Is Null TEN' 'NULL' 'ELSE' '' '' '' '' ' ' 'Replace (' Name ',' '' '', '' '' '' '' '' ' ' '' '' '' ' ' end '
WHEN Xtype in (175)
THEN 'CASE WHEN' NAME 'is NULL THEN' '' '' '' ELSE ' ' '' '' '' ' ' Cast (Replace (' Name ', '' '' '" , '' '' '' '' '') AS Char (' Cast As Varchar) ') '' '' '' '' 'End'
When Xtype in (239)
THEN 'CASE WHEN' NAME 'IS NULL THEN' 'NULL' 'ELSE' '' '' '' '' ' ' 'CAST (Replace (' Name ',' '' '" ',' '' '' '' '') AS Char (' CAST (Length AS VARCHAR) ') '' '' '' '' 'end'lse' '' Null ' '' '
End as cols, name
From syscolumns
Where id = Object_id (@tablename)
T
Set @SQL = 'SELECT' '' INSERT INTO [' @tablename '] ip (@ SQL, LEN (@SQL) -1) ')' Left (@ Sqlvalues, Len (@sqlvalues) -4 ) ')' 'from' @ TableName
--Print @SQL
EXEC (@SQL)
end
Go
- ================================================================================================================================================================== ======
- The stored procedure of generating an INSERT statement according to the data in the table
- Establish a stored procedure, execute 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
--Hen a.xtype = 173 TEN 'CASE WHEN' A.NAME 'IS NULL TEN' 'NULL' 'ELSE' 'Convert (Varchar (VARCHAR (4), A.LENGTH * 2 2 ) '),' a.name ')' 'end'
When a.xtype = 104 THEN 'CASE WHEN' A.NAME 'IS NULL TEN' 'NULL' 'ELSE' 'Convert (varchar (1),' a.name ')' 'end'
When a.XTYPE = 175 THEN 'CASE WHEN' A.NAME 'IS NULL THEN' '' '' 'ELSE' '' '' '' ' ' 'Replace (' a.name ',' '' '' '' ',' '' '' '' '' '' ' ' '' '' ' ' End '
When a.xtype = 61 THEN 'CASE WHEN' A.NAME 'IS NULL THEN' '' '' '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 Ten' 'Null' 'Else' 'Convert (VARCHAR (23),' A.NAME ', 2)' 'end'
When a.XTYPE = 56 THEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (varchar (11),' a.name ')' 'end'
When a.XTYPE = 60 THEN 'CASE WHEN' A.NAME 'IS NULL TEN' 'NULL' 'ELSE' 'Convert (varchar (22),' a.name ')' End '
When a.xtype = 239 THEN 'CASE WHEN' A.NAME 'IS NULL TEN' '' '' 'ELSE' '' '' '' '' ' ' Replace (' a.name ', ' '' '' '' ',' '' '' '' '' '' ' ' '' '' ' ' End '
When a.xtype = 108 TEN 'CASE WHEN' A.NAME 'Is Null Ten' '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 TEN 'CASE WHEN' A.NAME 'Is Null Ten' '' '' 'Else' '' '' '' ' ' 'Convert (Varchar (23),' A .name ', 121)' '' '' '' '' 'end'
When a.XTYPE = 52 THEN 'CASE WHEN' A.NAME 'Is Null Ten' '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 Ten' 'Null' 'Else' 'Convert (varchar (6),' A.NAME ')' 'end'
- when a.xtype = 165 THEN 'CASE WHEN' A.NAME 'IS NULL TEN' 'NULL' 'ELSE' 'Convert (Varchar (' Convert (varchar (4), A.Length * 2 2 ) '),' a.name ')' 'end'when a.xtype = 167 TEN' CASE WHEN ' A.NAME ' Is Null Ten '' Null 'Else' '' '' '' '' ' ', '' '' '' '', '' '' '' '' '' ' ' '' '' '' '' '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
Go
