查找表列的信息-CSDN博客

--判断数据库是否存在
if exists(select * from master..sysdatabases where name=N '库名 ')
print 'exists '
else
print 'not exists '

---------------
-- 判断要创建的表名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[表名] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
-- 删除表
drop table [dbo].[表名]
GO

---------------
--判断要创建临时表是否存在
If Object_Id( 'Tempdb.dbo.#Test ') Is Not Null
Begin
print '存在 '
End
Else
Begin
print '不存在 '
End

---------------
-- 判断要创建的存储过程名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[存储过程名] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
-- 删除存储过程
drop procedure [dbo].[存储过程名]
GO

---------------
-- 判断要创建的视图名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[视图名] ') and OBJECTPROPERTY(id, N 'IsView ') = 1)
-- 删除视图
drop view [dbo].[视图名]
GO

---------------
-- 判断要创建的函数名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[函数名] ') and xtype in (N 'FN ', N 'IF ', N 'TF '))
-- 删除函数
drop function [dbo].[函数名]
GO

--为判断表是否存在字段
IF EXISTS(SELECT 1 FROM dbo.SYSOBJECTS WHERE ID=OBJECT_ID(N'[dbo].[表名]') AND OBJECTPROPERTY(ID,N'IsUserTable') = 1 ) AND -- 判断表是否存在
NOT EXISTS
(
SELECT 1 FROM dbo.SYSCOLUMNS WHERE Name='字段名' AND
ID IN ( SELECT ID FROM dbo.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[表名]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
)
BEGIN
ALTER TABLE [dbo].[表名] ADD [字段名] INT DEFAULT(0)
END

获取数据库表清单：
select top 1000
ROW_NUMBER() OVER (ORDER BY a.object_id) AS No,
a.name AS 表名,
isnull(g.[value],'-') AS 说明
from
sys.tables a left join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = 0)

No 表名说明
1 test_table 测试表
2 test_main -

获取数据库表的字段清单 [这里是查看 test_table 表的字段]
SELECT
a.column_id AS No,
a.name AS 列名,
isnull(g.[value],'-') AS 说明
FROM
sys.columns a left join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = a.column_id)
WHERE
object_id =
(SELECT object_id FROM sys.tables WHERE name = 'test_table')

No 列名说明
1 Test_ID 主键(自增长)
2 Test_Key 种类
3 Test_Value 数值
4 Test_Type 内部类型
5 Test_BelongTo 从属关系
6 Test_Grade 等级
7 Test_Remark 备注
8 Test_Visible 是否可见

-----

更加详细一点的信息

SELECT
   col.name AS 列名,
   typ.name as 数据类型,
   col.max_length AS 占用字节数,
   col.precision AS 数字长度,
   col.scale AS 小数位数,
   col.is_nullable AS 是否允许非空,
   col.is_identity AS 是否自增,
   case when exists
      ( SELECT 1
        FROM
          sys.indexes idx
            join sys.index_columns idxCol
            on (idx.object_id = idxCol.object_id)
         WHERE
            idx.object_id = col.object_id
            AND idxCol.index_column_id = col.column_id
            AND idx.is_primary_key = 1
       ) THEN 1 ELSE 0 END AS 是否是主键
FROM
sys.columns col left join sys.types typ on (col.system_type_id = typ.system_type_id)
WHERE
col.object_id =
(SELECT object_id FROM sys.tables WHERE name = 'test_table')

SQL Server 2005 的 Microsoft SQL Server Management Studio Express

没有带列名一起复制。凑合着看看吧，就不手动写列名了。

Test_ID   int   4   10   0   0   1   1   主键(自增长)
Test_Key   varchar   10   0   0   0   0   0   种类
Test_Value   varchar   20   0   0   0   0   0   数值
Test_Type   int   4   10   0   0   0   0   内部类型
Test_BelongTo   int   4   10   0   1   0   0   从属关系
Test_Grade   int   4   10   0   1   0   0   等级
Test_Remark   varchar   50   0   0   1   0   0   备注
Test_Visible   bit   1   1   0   1   0   0   是否可见