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) Table name, a.colorder field number, a.name field name, (Case When ColumnProperty (A.ID, A.NAME, 'IsIndentity') = 1 THEN '√'lse' 'end) Identification (Case When (Select Count (*) from sysobjects where (select name from sysindexes where (id = a.id) and (SELECT Indid from sysindexkeys) WHERE (ID = a.id) AND (Colid in (Select Colid from syscolumns where (id = a.id) and (name = a.name))))))))))))> 0 THEN '√' Else '' end) Primary key, B.Name type, A.Length occupies bytes, columnproperty (A.ID, a.Name, 'precision ") AS length, ISNULL (ColumnProperty (A.ID, A. .name, 'scale'), 0) AS number, (Case When a.isnullable = 1 Then '√'lse' 'end) Allow empty, isnull (E.TE XT, '') Default, isnull (g. [value], '') AS field Description from syscolumns a left join systems 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 SYSCOMents 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ----
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, @ 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, systypes 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, @ 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)) "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) = ' 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 ' @ FLD ' 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 *, L Trim (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 endclose read_cursor deallocate read_cursor Go
SET ANSI_NULLS ON Go Set NoCount Off Go Select Count (*) from #t Drop Table #t Gosect 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 username and password 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
- Create 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 = 'value (' ' "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) TEN' Case WHEN ' NAME ' Is Null Then '' Null '' Else ' ' '' '' ' ' 'CAST (' Name 'As Varchar)' ' ' '' '' 'End' When Xtype In (167) THEN 'Case when' NAME 'IS NULL THEN' '' '' ELSE ' ' '' '' ' ' 'Replace (' Name ',' '' '' ", '' '' '' '' '' '' '' '' '' '' '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' '' 'ELSE' '' '' '' ' ' CAST (Replace (' Name ', '' '' ", '' '' '' '' ') AS Char (' CAST (Length As Varchar) ') ' '' '' '' End 'When Xtype In (239) THEN' Case WHEN ' NAME ' IS NULL THEN '' '' ELSE ' ' '' '' '' '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) EndGo
- ================================================================================================================================================================== ====== - 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 - when A.XTYPE = 173 TEN A. xi ' a.Name ' Is Null Then '' Null '' Else ' ' Convert (VARCHAR (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 TEN '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 '' '' '' ELSE ' ' '' '' '' ' 'Convert (varchar (23),' a.name '
121) ' ' End 'When A.XTYPE = 106 TEN' 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 TEN 'CASE WHEN' A.NAME ' Is Null Then '' Null '' Else ' ' Convert (varchar (11), ' a.name ') 'End' When A.XTYPE = 60 TEN 'CASE WHEN' A.NAME 'IS NULL Then '' Null 'Else' 'Convert (VARCHAR (22),' a.name ')' 'end' when a.xtype = 239 TEN 'CASE WHEN A.NAME ' Is Null Then ' 'Null' 'Else' '' '' '' '' 'Replace (' a.name ',' '' '' '', '' '' '' '' '' ' ) ' ' '' '' '' end 'when a.xtype = 108 TEN' CASE WHEN ' A.NAME ' Is Null Then '' Null '' Else ' ' Convert (VARCHAR ' Convert (VARCHAR (4), A.XPREC 2) '), ' a.name ') ' ' end 'when a.xtype = 231 TEN' CASE WHEN ' A.NAME ' IS NULL Then ''
NULL '' '' '' ' ' '' '' '' '' '' ',' '' '' '' '' ') ' ' '' '' '' end 'when a.xtype = 59 TEN' 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 THEN '' '' '' 'ELSE' '' '' ' '' ' ' Convert (VARCHAR (23), ' A.NAME ', 121) ' ' '' '' '' 'end' when a.xtype = 52 THEN 'CASE WHEN ' A.NAME ' IS NULL THEN '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 twen' 'Null' 'Else' 'Convert (varchar (6),' a.name ')' 'end' - when a.xtype = 165 TEN 'CASE WHEN' A .name 'is null dam' 'Null' 'Else' 'Convert (VARCHAR (VARCHAR (4), A.LENGTH * 2 2),' a.name ')' 'end'
