sql 脚本的 指定数据库所有表的查询及指定表的所有列名的查询 / sql修改表名及修改列名称

-- 表重命名
EXEC sp_rename 'Test','Temp'
-- 列重命名
EXEC  sp_rename  'Temp.[TestID]' ,  'Cid'

-- 所有数据库表查询

select [id], [name] from [sysobjects] where [type] = 'u' order by [name]


--读取指定表的所有列名

select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='employee')

SELECT
   obj.name AS 表名,
   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)
INNER JOIN [sysobjects] AS obj WITH(NOLOCK) ON obj.id = col.[object_id]
WHERE obj.[type] = 'u' 
ORDER BY obj.name
-- AND obj.name = 'employee'
--WHERE col.object_id = (SELECT object_id FROM sys.tables WHERE name = 'employee')