sql查询表结构，过程，视图，主键，外键，约束
一、表结构查询
SELECT TOP (100) PERCENT a.name AS zdm,COLUMNPROPERTY(a.id, a.name, 'IsIdentity') AS bs ,
CASE WHEN EXISTS (SELECT 1 from dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid)
THEN '1' ELSE '0' END AS zj , b.name AS lx, a.length AS cd, COLUMNPROPERTY(a.id, a.name,'PRECISION')
AS jd, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS xsws,a.isnullable AS yxk, ISNULL(e.text, '')
AS mrz, ISNULL(g.value, '') AS zdsm from dbo.syscolumns AS a LEFT OUTER JOIN dbo.systypes AS b ON a.xtype = b.xusertype
INNER JOIN dbo.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 LEFT OUTER JOIN
dbo.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN sys.extended_properties AS g
ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN sys.extended_properties
AS f ON d.id = f.major_id AND f.minor_id = 0 where d .name='查询的表名'
二、
-- 查询存储过程
select CASE a.xtype WHEN 'p' THEN '存储过程' end as lx ,a.name, b.text from sysobjects a left outer join syscomments b on a.id = b.id where xtype='p'
--查询视图
select CASE a.xtype WHEN 'v' THEN '视图' end as lx,a.name , b.text from sysobjects a left outer join syscomments b on a.id = b.id where xtype='v'
--主键，外键，约束
select
CASE a.xtype WHEN 'PK' THEN '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束'
END AS lx,a.name AS name,
b.text from sysobjects a left outer join syscomments b on a.id = b.id
where (a.xtype IN ( 'C', 'F','PK')) AND
(OBJECTPROPERTY(a.id, N'IsMSShipped') = 0) and a.parent_obj=(select id from sysobjects where name = 'table_2')
环境是用的sql2008
其中涉及到的表 与视图 过程的名称在sql的帮助中能够查
相关文档：
在开发不论是WEB还是Winform程序中，数据分页是经常遇到的问题，要是在代码中实现此过程，显然降低了服务器的效率，可以使用以下存储过程来实现。
CREATE PROCEDURE pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)= ......
恢复模式（Recovery Model）旨在控制事务日志维护。恢复模式说明了工作丢失的风险，能否恢复到时点？
SQL Server数据库有三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。
相对于简单恢复模式而言，完整恢复模式和大容量日志恢复模式提供了更强的数据保护功能。这些恢复模式都是基于备份事务日� ......
from: http://blog.163.com/ck275601774/blog/static/1230468012009631113559291/
--日期转换参数
select CONVERT(varchar,getdate(),120)
--2009-03-15 15:10:02
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
--20090315151201
select CONVERT(varchar(12) , getdate ......
1 :普通SQL语句可以用Exec执行
例: Select * from tableName
Exec('select * from tableName')
& ......
SQL当前日期获取技巧
select getdate() //2003-11-07 17:21:08.597
select convert(varchar(10), getdate(),120) //2003-11-07
select convert(char(8),getdate(),112) ......
