-- ===================================================== ====================
--ListSQL ServerAll tables, field names, primary keys, types, lengths, decimal places, and other information
--Run in the query analyzer.,You can generate a table and export itExcelMedium
-- ===================================================== ====================
Select
(Case when a. colorder = 1 then D. Name else ''end)Table Name,
A. colorderFIELD No.,
A. NameField name,
(Case when columnproperty (A. ID, A. Name, 'isidentity ') = 1 then'√'Else' end)Identifier,
(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. NameType,
A. LengthBytes occupied,
Columnproperty (A. ID, A. Name, 'precision ')Length,
Isnull (columnproperty (A. ID, A. Name, 'Scale'), 0)Decimal places,
(Case when a. isnullable = 1 then'√'Else' end)Null allowed,
Isnull (E. Text ,'')Default Value,
Isnull (G. [value], '')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 -------------------------------------------------------------------------------------------------
ListSQL ServerAll tables, field definitions, types, lengths, values, and other information
And exportExcelMedium
-- ===================================================== ====================
-- 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 ()
--Modify the user name and password ExportExcelMedium
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
-- ===================================================== ====================
--Generate based on table dataInsertStatement Stored Procedure
--Create a stored procedure and executeSpgeninsertsqlTable Name
--ThanksPlayyuer
-- ===================================================== ====================
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
-- ===================================================== ====================
--Generate based on table dataInsertStatement Stored Procedure
--Create a stored procedure and executeProc_insertTable Name
--ThanksSky_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