获取SQL SERVER2012的数据库表结构代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】获取SQL SERVER2012的数据库表结构

作者:奕萱 / 发布于2015/6/19/ 1014


	SELECT
	(case when a.colorder=1 then d.name else '' end) N'表名',
	a.colorder N'字段序号',
	a.name N'字段名',
	(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '&radic;'else '' end) N'标识',
	(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 '&radic;' else '' end) N'主键',
	b.name N'类型',
	a.length N'占用字节数',
	COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
	isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
	(case when a.isnullable=1 then '&radic;'else '' end) N'允许空',
	isnull(e.text,'') N'默认值',
	isnull(g.[value],'') AS N'字段说明'
	into ##tx

	FROM syscolumns a left join systypes 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 sys.extended_properties g
	-- on a.id=g.id AND a.colid = g.smallid
	on a.id=g.major_id AND a.colid = g.major_id
	order by object_name(a.id),a.colorder

	declare @db varchar(30),@sql varchar(3000)
	set @db=db_name()
	set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -c -Usa -P '''
	--print @sql
	exec(@sql)

	select * from ##tx

试试其它关键字

　数据库表结构　

同语言下

可能有用的

奕萱贡献的其它代码(10)