SQL 系统表操作

--查看所有表名

SELECT name, id, xtype, status, crdate, type, version FROM sys.sysobjects
sysrowsetcolumns	4		S 	0	2005-10-14 01:36:15.923	S 	0
V_BomSubInv_Latest_Join	107141		V 	0	2010-12-13 11:27:31.590	V 	0
P_tLocateSet_GetModel	2203158		P 	0	2011-10-27 10:06:03.797	P 	0
DF_tMMPOrders_fMSchQty	44019388	D 	0	2012-02-20 00:36:58.293	D 	0
tCPlans_Detail		63495455	U 	0	2012-02-20 00:36:57.043	U 	0

--查询指定表的所有字段

SELECT name, typestat, xusertype, length, language, collation, tdscollation FROM sys.syscolumns WHERE id= OBJECT_ID('tFilter')
ConditionID	1	56	4	0	NULL			0x0000000000
FormID		3	167	50	0	Chinese_PRC_CI_AS	0x0408D00000
cConditionName	3	167	50	0	Chinese_PRC_CI_AS	0x0408D00000
cTitle		3	167	50	0	Chinese_PRC_CI_AS	0x0408D00000
OrderID		0	56	4	0	NULL			0x0000000000
cEditType	3	167	20	0	Chinese_PRC_CI_AS	0x0408D00000
cReferCode	2	167	50	0	Chinese_PRC_CI_AS	0x0408D00000
cComparison	3	167	50	0	Chinese_PRC_CI_AS	0x0408D00000
cDataSource	3	167	50	0	Chinese_PRC_CI_AS	0x0408D00000
bVisible	1	104	1	0	NULL			0x0000000000
bMust		1	104	1	0	NULL			0x0000000000
bInterval	1	104	1	0	NULL			0x0000000000
bMulChoice	1	104	1	0	NULL			0x0000000000
cEnum		2	167	1000	0	Chinese_PRC_CI_AS	0x0408D00000

--查询基础表和视图

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
hyqzData_anta1	dbo	v_TechsYPositon	VIEW
hyqzData_anta1	dbo	tCompareQuotes	BASE TABLE

--查询视图

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS
hyqzData_anta1	dbo	V_BomSubInv_Latest_Join	     	CREATE View [dbo].[V_BomSubInv_Latest_Join] ...  	NONE	NO
hyqzData_anta1	dbo	v_TechsYPositon	    		create View [dbo].v_TechsYPositon  As  Sele ...    	NONE	NO

--查询视图中所运用到的字段

SELECT     TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
    CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX,, DATETIME_PRECISION, CHARACTER_SET_CATALOG,
    CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG,, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA,
    DOMAIN_NAME FROM INFORMATION_SCHEMA.COLUMNS
hyqzData_anta1	dbo	V_BomSubInv_Latest_Join	iStage	2	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
hyqzData_anta1	dbo	V_BomSubInv_Latest_Join	cStyleCode	3	NULL	YES	varchar	30	30	NULL	NULL	NULL	NULL	NULL	NULL	cp936	NULL	NULL	Chinese_PRC_CI_AS	NULL	NULL	NULL