9CBS - Document Center - Database Developer reading: 3844 Comments: 0 Participation in reviews
Title Collection Several SQL Server Statements and Storage Procedures Select from Herman_chow BLOG Keyword Collection Several SQL Server Statements and Storage Procedures - ===================== ================================== - list SQL Server all tables, field names, primary keys, type, length , Small digits and other information - 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, 'ISIDENTITY') = 1 Ten '√'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))))))))))))))))))> 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.Inullable = 1 Then '√'ELSE' 'end) Allows empty, isnull (E.TEXT,' ') default value, isnull (g. [Value],' ') AS field Description from syscolumns a left a.xtype = b.xUsertypeinner Join Sysobjects D on A.ID = D.ID and D.XTYPE =' u 'and d.name <> DTProperties'Left Join Syscomments eon a.cdefault = E.Idleft Join sysproperties gon a.id = g.id and a.colid = g.smallid Order by a.id, a.colorder -------------- -------------------------------------------------- -------------------------------- list all tables, field definitions, length, one value, etc. Information and export to Excel - ========================================== ============ - Export all substribed and one sample value - JAN-13-2003, Dr.zhang - ============== ======================================== run in the query analyzer: set ANSI_NULLS OFF Goset Nocount ONGO SET LANGUAGE 'Simplified Chinese'godeClare @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 #TFROM Syscolumns A, Systems, Sysobjects D Where A.XTYPE = B.XUSERTYPE and A.ID = D.ID and D.XTYPE = 'u'
DECLARE read_cursor CURSORFOR 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.com read_cursor fetch next from read_cursor INTO @ TBL, @@ fetch_status <> -1) --- FAILESBEGIN IF (@@ fetch_status <> -2) - Missing Begin Set @ SQL = N'SET @ Maxlen = (Select Max (LEN (Cast (' @ FLD ' AS NVARCHER)) "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) Outpu T ', @ 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 *, 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, @ fldEND CLOSE read_cursorDEALLOCATE read_cursorGO SET ANSI_NULLS ONGOSET NOCOUNT OFFGOselect count (*) from #tDROP TABLE #tGO 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 users Name and password export to Excel set @ SQL = 'exec master.dbo.xp_cmdshell' 'bcp ..dbo. ## tx out c: /' @ db '_ exp.xls -w -c936 -
USA -PSA '' 'PRINT @SQLEXEC (@SQL) godrop table ## txgo - =============================== ======================== - According to the stored procedure of the INSERT statement according to the table data - establish the stored procedure, execute the spGeninsertsql table name - Thank Playyuer - ================================================================================================================================================================== ====== Create Proc spgeninsertsql (@tablename varchar (256)) asbegin 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) TEN' CASE WHEN ' NAME ' IS NULL THEN '' '' 'ELSE' '' '' '' '' ' ' Replace (' Name ', '' '' '' '' '' '' '' '' '' '' '' '' End 'When Xtype In (231 ) THEN 'CASE WHEN' NAME '' 'ELSE' '' '' N '' '' '' ' ' 'Replace (' Name ',' '' '' " '' '' '' '' '' '' 'end' WHEN Xtype in (175) TEN 'CASE WHEN NAME ' is Null Then ' 'Null' 'Else' '' '' '' '' 'Cast (REPLACE (' Name ',' '' '' ',' '' '' '' '' '" AS Char (' CAST (Length As Varchar) ')) '' '' '' 'End' When Xtype In (239) TEN 'CASE WHEN' NAME 'Is Null Then' ' Null '' 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) end go - ===== ========================================================= - - Based on the stored procedure of the INSERT statement based on the data in the table - establish the stored procedure, execute the proc_insert table name - Thank sky_BLUE - ====================== =============================== Create proc proc_insert (@tablename varchar (256)) asbegin 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 'CASE WHEN A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (VARCHAR (' Convert (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 TEN' '' '' 'ELSE' '' '' '' ' ' 'Replace (' a.name ',' ' '' '' '', '' '' '' '' ' ' '' '' '' 'end' when a.xtype = 61 TEN 'CASE WHEN' A .name 'is NULL THEN' '' '' '' '' ' ' CONVERT (VARCHAR (23), ' A.NAME ', 121) ' ' '' '' '' '' End 'When A. type = 106 Then' Case When ' A.Name ' Is Null Ten '' Null '' Else ' ' Convert (VARCHAR (VARCHAR (VARCHAR (4 ), A.XPREC 2) '),' a.name ')' 'end' when a.xtype = 62 THEN 'CASE WHEN' A.NAME 'Is Null Then' 'Null'
'Else' 'Convert (VARCHAR (23),' A.NAME ', 2)' 'end' when a.xtype = 56 TEN 'CASE WHEN' A.NAME 'Is Null TEN' '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 Then' Case When ' A.Name ' Is Null Ten '' Null '' Else ' ' Convert (VARCHAR (VARCHAR (VARCHAR (4 ), A.XPREC 2) '),' a.name ')' 'end' when a.xtype = 231 TEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' ELSE ' ' '' '' '' '' ',' '' '' '', '' '' '' '' '' ' ' '' '' '' '' end 'when a.xtype = 59 TEN' CASE WHEN ' A.NAME ' IS NULL TEN '' NULL '' ELSE ' '
Convert (VARCHAR (23), ' A.NAME ', 2) ' ' end 'when a.xtype = 58 TEN' CASE WHEN ' A.NAME ' IS NULL THEN 'NULL' '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 Ten '' Null '' Else ' ' Convert (varchar (6), ' a.name ') ' ' end '- when a.xtype = 165 THEN' Case WHEN ' A.NAME ' IS NULL THEN '' NULL '' ELSE ' ' Convert (VARCHAR (VARCHAR (4), A.LENGTH * 2 2) '),' a.Name ')' 'end' when a.XTYPE = 167 TEN 'CASE WHEN' A.NAME 'IS NULL TEN' '' '' '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 <> 35 and A.XTYPE < > 36) T ORDER BY Colid Select @ SQLSTR = @ SQLSTR LEFT (@ Sqlstr2) -1) ')' Left (@ Sqlstr1, Len (@ SQLSTR1) -3) ')') From ' @ TableName - print @sqlstr EXEC (@sqlstr) Set NoCount Offendgo Author BLOG:
http://blog.9cbs.net/herman_chow/
