--表和視圖
select 名稱=[Name],類型=case when type ='u' then '表' when type='v' then '視圖' end from
sysobjects where xtype in ('u','V')
order by xtype
----------------------------------------------------------------------
名稱 類型
-------------------------------------------------------------------------------------------------------------------------------- ----
table2 表
S_AccreditL 表
Mtl_StockAnalyze 表
ProcBasic 表
PBy_Customer 表
PCompEveryDay 表
PExtMain 表
dtproperties 表
PExtTab 表
_tmpOutQnty 表
A_tmpCompany 表
新資料表 表
Mtl_MatKind 表
Acc_tblReturnGoodsAndDiscount 表
Company 表
customer 視圖
sal_prodbasic 視圖
VMSupply 視圖
VMatName 視圖
select 名稱=[Name],類型=case when type ='u' then '表' when type='v' then '視圖' end from
sysobjects where xtype in ('u','V')
order by xtype
----------------------------------------------------------------------
名稱 類型
-------------------------------------------------------------------------------------------------------------------------------- ----
table2 表
S_AccreditL 表
Mtl_StockAnalyze 表
ProcBasic 表
PBy_Customer 表
PCompEveryDay 表
PExtMain 表
dtproperties 表
PExtTab 表
_tmpOutQnty 表
A_tmpCompany 表
新資料表 表
Mtl_MatKind 表
Acc_tblReturnGoodsAndDiscount 表
Company 表
customer 視圖
sal_prodbasic 視圖
VMSupply 視圖
VMatName 視圖
---標准字典格式
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表描述=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序號=a.colorder,
字段名=a.name,
是否為空=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主鍵=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
類型=b.name,
占用字節=a.length,
長度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小位=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允許空=case when a.isnullable=1 then '√'else '' end,
默認值=isnull(e.text,''),
字段說明=isnull(g.[value],'')
---INto Admin_find
FROM syscolumns a
inner join sysobjects d on a.id=d.id
left join systypes b on a.xtype=b.xusertype
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
where d.xtype='U' and d.name<>'dtproperties'
--where d.name='要查的表' --如果只查?指定表,加上此件
order by a.id,a.colorder
-----------------test
表名 表描述 字段序號 字段名 是否為空 主鍵 類型 占用字節 長度 小位 允許空 默認值 字段說明 Dr_ClassWork 1 WkID √ tinyint 1 3 0
2 WkName √ char 6 6 0
Dr_CutStReason 1 CutID √ tinyint 1 3 0
2 CutReason char 10 10 0
Car_Repair 1 CarId √ char 10 10 0
2 RepairDate √ datetime 8 23 3
3 RepairNote varchar 50 50 0 √
4 RunLast int 4 10 0 √
5 RunNow int 4 10 0 √
6 TakeMoney float 8 53 0 √
7 CheckMan char 10 10 0 √
Dr_CutDoWay 1 WayID √ tinyint 1 3 0
2 DoWay char 12 12 0
Dr_StitchType 1 StTypeID √ tinyint 1 3 0
2 StType char 8 8 0
3 StMeasure √ real 4 24 0
Dr_StitchSort 1 StSortID √ tinyint 1 3 0
2 StSort char 8 8 0
Dr_Machine 1 FacID √ tinyint 1 3 0
2 Fac char 8 8 0
3 MachineID √ tinyint 1 3 0
Dr_MachOutputDtl 1 OpPaperID √ int 4 10 0 (1)
2 MachineID tinyint 1 3 0 √
3 WkID tinyint 1 3 0 √
4 PartNum char 10 10 0 √
5 Revision char 5 5 0 √
6 LotNum char 10 10 0 √
7 Builtdate datetime 8 23 3 √ (getdate())
8 Prodfreq int 4 10 0 √
9 Output float 8 53 0 √
10 Finished smallint 2 5 0 √
11 Note varchar 50 50 0 √
Dr_MachOutputBas 1 OpPaperID √ int 4 10 0
2 MachineID tinyint 1 3 0 √
3 WkID tinyint 1 3 0 √
4 Builtdate datetime 8 23 3 √
5 Confirmdate datetime 8 23 3 √
6 Finished tinyint 1 3 0 √
TABLE1 1 StPaperID char 10 10 0
2 StMeasure real 4 24 0 √
3 MachinID tinyint 1 3 0 √
a1 1 A1 nvarchar 510 255 0 √
2 A2 nvarchar 510 255 0 √
3 A3 nvarchar 510 255 0 √
4 A4 nvarchar 510 255 0 √
5 A5 nvarchar 510 255 0 √
6 A6 nvarchar 510 255 0 √
7 A7 nvarchar 510 255 0 √
8 A8 float 8 53 0 √
9 A9 nvarchar 510 255 0 √
Car_Join 1 CarId √ char 10 10 0
2 DrvId √ varchar 12 12 0 √
a2 1 A1 nvarchar 510 255 0 √
2 A2 nvarchar 510 255 0 √
3 A3 nvarchar 510 255 0 √
4 A4 nvarchar 510 255 0 √
5 A5 nvarchar 510 255 0 √
6 A6 nvarchar 510 255 0 √
7 A7 nvarchar 510 255 0 √
8 A8 float 8 53 0 √
9 A9 nvarchar 510 255 0 √
a3 1 A1 nvarchar 510 255 0 √
2 A2 nvarchar 510 255 0 √
3 A3 nvarchar 510 255 0 √
4 A4 nvarchar 510 255 0 √
5 A5 nvarchar 510 255 0 √
6 A6 nvarchar 510 255 0 √
7 A7 nvarchar 510 255 0 √
8 A8 float 8 53 0 √
9 A9 nvarchar 510 255 0 √
a4 1 A1 nvarchar 510 255 0 √
2 A2 nvarchar 510 255 0 √
3 A3 nvarchar 510 255 0 √
4 A4 nvarchar 510 255 0 √
5 A5 nvarchar 510 255 0 √
6 A6 nvarchar 510 255 0 √
7 A7 nvarchar 510 255 0 √
8 A8 float 8 53 0 √
9 A9 nvarchar 510 255 0 √
a5 1 A1 nvarchar 510 255 0 √
2 A2 nvarchar 510 255 0 √
3 A3 nvarchar 510 255 0 √
4 A4 nvarchar 510 255 0 √
5 A5 nvarchar 510 255 0 √
6 A6 nvarchar 106 53 0 √
7 A7 nvarchar 510 255 0 √
8 A8 float 8 53 0 √
9 A9 nvarchar 510 255 0 √
a6 1 A1 nvarchar 510 255 0 √
2 A2 nvarchar 510 255 0 √
3 A3 nvarchar 510 255 0 √
4 A4 nvarchar 510 255 0 √
5 A5 nvarchar 510 255 0 √
6 A6 nvarchar 106 53 0 √
7 A7 nvarchar 510 255 0 √
8 A8 float 8 53 0 √
9 A9 nvarchar 510 255 0 √
tmpqnty 1 A1 √ nvarchar 510 255 0 √
2 A2 nvarchar 510 255 0 √
3 A3 nvarchar 510 255 0 √
4 A4 nvarchar 510 255 0 √
5 A5 nvarchar 510 255 0 √
6 A6 nvarchar 510 255 0 √
7 A7 nvarchar 510 255 0 √
8 A8 float 8 53 0 √
9 A9 nvarchar 510 255 0 √
aims 1 PHJOB float 8 53 0 √
2 PHLOC nvarchar 510 255 0 √
3 PHPROD nvarchar 510 255 0 √
4 PHQTY float 8 53 0 √
5 PHQUM float 8 53 0 √
6 PHTAG nvarchar 510 255 0 √
7 PHTUM nvarchar 510 255 0 √
8 PHUM float 8 53 0 √
9 PHWHS √ nvarchar 510 255 0 √
getNum 1 uid √ char 5 5 0
2 Add_Qnty int 4 10 0 (0)
3 T_month char 2 2 0 √
reporttable 1 date char 10 10 0 √
2 A1 int 4 10 0 √
3 price1 numeric 9 15 2 √
4 A2 int 4 10 0 √
5 price2 numeric 9 15 2 √
6 B1 int 4 10 0 √
7 price3 numeric 9 15 2 √
8 price4 numeric 9 15 2 √
9 C1 int 4 10 0 √
10 C2 int 4 10 0 √
11 price5 numeric 9 15 2 √
12 D1 int 4 10 0 √
13 price6 numeric 9 15 2 √
14 Total int 4 10 0 √
15 pricetotal numeric 9 18 2 √
T_CF 1 A1 √ char 2 2 0
2 A2 char 10 10 0
3 A3 char 8 8 0 √
4 A4 char 8 8 0 √
5 A5 numeric 5 4 2 √
T_DataSource 1 B2 char 2 2 0 √
2 C2 √ char 10 10 0 √
3 C3 char 10 10 0 √
4 C4 char 2 2 0 √
5 C5 char 2 2 0 √
6 C6 char 8 8 0 √