有用的sql语句

展开阅读全文

一些有用SQL Server语句和存储过程

06-05

1-- ======================================================rn 2rn 3--列出SQL SERVER 所有表,字段名,主键,类型,长度,小数位数等信息rn 4rn 5--在查询分析器里运行即可,可以生成一个表,导出到EXCEL中rn 6rn 7-- ======================================================rn 8rn 9Select rn 10rn 11 (Case When A.Colorder=1 Then D.Name Else '' End)表名,rn 12rn 13 A.Colorder 字段序号,rn 14rn 15 A.Name 字段名,rn 16rn 17 (Case When Columnproperty( A.Id,A.Name,'Isidentity')=1 Then '√'Else '' End) 标识,rn 18rn 19 (Case When (Select Count(*)rn 20rn 21 From Sysobjectsrn 22rn 23 Where (Name Inrn 24rn 25 (Select Namern 26rn 27 From Sysindexesrn 28rn 29 Where (Id = A.Id) And (Indid Inrn 30rn 31 (Select Indidrn 32rn 33 From Sysindexkeysrn 34rn 35 Where (Id = A.Id) And (Colid Inrn 36rn 37 (Select Colidrn 38rn 39 From Syscolumnsrn 40rn 41 Where (Id = A.Id) And (Name = A.Name))))))) Andrn 42rn 43 (Xtype = 'Pk'))>0 Then '√' Else '' End) 主键,rn 44rn 45 B.Name 类型,rn 46rn 47 A.Length 占用字节数,rn 48rn 49 Columnproperty(A.Id,A.Name,'Precision') As 长度,rn 50rn 51 Isnull(Columnproperty(A.Id,A.Name,'Scale'),0) As 小数位数,rn 52rn 53 (Case When A.Isnullable=1 Then '√'Else '' End) 允许空,rn 54rn 55 Isnull(E.Text,'') 默认值,rn 56rn 57 Isnull(G.[Value],'') As 字段说明 rn 58rn 59From Syscolumns A Left Join Systypes B rn 60rn 61On A.Xtype=B.Xusertypern 62rn 63Inner Join Sysobjects D rn 64rn 65On A.Id=D.Id And D.Xtype='U' And D.Name<>'Dtproperties'rn 66rn 67Left Join Syscomments Ern 68rn 69On A.Cdefault=E.Idrn 70rn 71Left Join Sysproperties Grn 72rn 73On A.Id=G.Id And A.Colid = G.Smallid rn 74rn 75Order By A.Id,A.Colorderrn 76rn 77列出SQL SERVER 所有表、字段定义,类型,长度,一个值等信息rn 78rn 79并导出到Excel 中rn 80rn 81-- ======================================================rn 82rn 83-- Export all user tables definition and one sample valuern 84rn 85-- jan-13-2003,Dr.Zhangrn 86rn 87-- ======================================================rn 88rn 89在查询分析器里运行:rn 90rn 91SET ANSI_NULLS OFF rn 92rn 93GOrn 94rn 95SET NOCOUNT ONrn 96rn 97GOrn 98rn 99 rn100rn101SET LANGUAGE 'Simplified Chinese'rn102rn103gorn104rn105DECLARE @tbl nvarchar(200),@fld nvarchar(200),@sql nvarchar(4000),@maxlen int,@sample nvarchar(40)rn106rn107 rn108rn109SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #trn110rn111FROM syscolumns a, systypes b,sysobjects d rn112rn113WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U' rn114rn115 rn116rn117DECLARE read_cursor CURSORrn118rn119FOR SELECT TableName,FieldName FROM #trn120rn121 rn122rn123SELECT TOP 1 '_TableName ' TableName,rn124rn125 'FieldName ' FieldName,'TypeName ' TypeName,rn126rn127 'Length' Length,'IS_NULL' IS_NULL, rn128rn129 'MaxLenUsed' AS MaxLenUsed,'Sample Value ' Sample,rn130rn131 'Comment ' Comment INTO #tc FROM #trn132rn133 rn134rn135OPEN read_cursorrn136rn137 rn138rn139FETCH NEXT FROM read_cursor INTO @tbl,@fldrn140rn141WHILE (@@fetch_status <> -1) --- failesrn142rn143BEGINrn144rn145 IF (@@fetch_status <> -2) -- Missingrn146rn147 BEGINrn148rn149 SET @sql=N'SET @maxlen=(SELECT max(len(cast('+@fld+' as nvarchar))) FROM '+@tbl+')'rn150rn151 --PRINT @sqlrn152rn153 EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUTrn154rn155 --print @maxlenrn156rn157 SET @sql=N'SET @sample=(SELECT TOP 1 cast('+@fld+' as nvarchar) FROM '+@tbl+' WHERE len(cast('+@fld+' as nvarchar))='+convert(nvarchar(5),@maxlen)+')'rn158rn159 EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUTrn160rn161 --for quickly rn162rn163 --SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+rn164rn165 --@tbl+' order by 1 desc ))' rn166rn167 PRINT @sqlrn168rn169 print @samplern170rn171 print @tblrn172rn173 EXEC SP_EXECUTESQL @sql,N'@sample nvarchar(30) OUTPUT',@sample OUTPUTrn174rn175 INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,rn176rn177 convert(nchar(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fldrn178rn179 ENDrn180rn 论坛

没有更多推荐了,返回首页