select max(len(子段名)) from 表名
解决方案 »
- 请教设计库的问题
- 急需帮助,先谢
- order by ----(复杂排序)
- alter column aaID int identity(1,2) not null 错误疑问
- 多关键字查询问题
- DTS數據導出問題
- sql驱动丢失 急问 在线等
- 访问远程服务器问题,很奇怪~~~
- 这个语句怎么写啊,我要疯了!只要搞出来了,什么都好说!
- 急*1000,这句SQL怎么写?(Select条件问题,菜鸟、专业挣分者勿入)
- 求:SQL server2000 单单要企业管理器的客户安装程序,******出手从来100分*********
- 问题简单,参与给分,解决追加!!!!急用!我的SQL Server服务管理器老是不启动!!!
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],'')
FROM syscolumns a
left join systypes b on a.xusertype=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
left join sysproperties f on d.id=f.id and f.smallid=0
--where d.name='table_name' --如果要查询特定table,加上这句
order by a.id,a.colorder
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],'')
FROM syscolumns a
left join systypes b on a.xusertype=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
left join sysproperties f on d.id=f.id and f.smallid=0
--where d.name='table_name' --如果要查询特定table,加上这句
order by a.id,a.colorder
drop procedure [dbo].[p_qry]
GO/*--char,varchar字段转换成nchar,nvarchar 查询将char,varchar转换成nchar,nvarchar后,数据会超长的表及字段--邹建 2005.01(引用请保留此信息)--*//*--调用示例
exec p_qry 1
--*/
create proc p_qry
@flag bit=0 --查询标志:
--转换时使用原来的定义长度,只查询定义长度超过4000,而且有记录超过此长度的表+列
--转换时使用原来的定义长度/2
as
set nocount oncreate table #t(
tablename sysname, --表名
fieldname sysname, --字段名
fieldtype sysname, --字段类型
deflen int, --目前定义的长度
Qry nvarchar(4000) --查询不规范数据使用的sql语句
)declare tb cursor local
for
SELECT N'select '
+replace(c.name,N']',N']]')
+N' from ['
+replace(o.name,N']',N']]')
+N'] where datalength(rtrim(['
+replace(c.name,N']',N']]')
+N']))'
+case when @flag=0 then N'>' else N'*2>' end
+case when c.prec>4000 then '4000' else rtrim(c.prec) end,
N') insert #t values(N'''
+replace(o.name,N'''',N'''''')
+N''','''+
+replace(c.name,N'''',N'''''')
+N''','''+
+replace(t.name,N'''',N'''''')
+N''','+rtrim(c.prec)
+N',@s)'
FROM syscolumns c,systypes t,sysobjects o
where o.id=c.id
and c.xusertype=t.xusertype
and objectproperty(o.id,'IsUserTable')=1
and o.status>=0
and t.name in('char','varchar') --要处理的数据类型
and c.prec>case when @flag=0 then 4000 else 0 end
order by o.id,c.coliddeclare @s1 nvarchar(4000),@s2 nvarchar(4000)
open tb
fetch tb into @s1,@s2
while @@fetch_status=0
begin
set @s2=N'if exists('+@s1+@s2
exec sp_executesql @s2,N'@s nvarchar(4000)',@s1
fetch tb into @s1,@s2
end
close tb
deallocate tb
select * from #t
由于 zjcxc(邹建) 大哥在前面的帖子已经给了100分这里就少给点。:)