sql:查询创建表的结构

浏览数：19 / 时间：2015年06月12日

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

--显示所有用户表：
--1
SELECT
  SCHEMA_NAME(schema_id) As 
SchemaName ,
  name 
As TableName 
from sys.tables 
ORDER BY 
      name
 
 
--2。alternate:
SELECT
  sch.name  
As SchemaName ,
  tbl.name 
As TableName 
from sys.tables tbl
inner join sys.schemas sch on 
tbl.schema_id = sch.schema_id
ORDER BY tbl.name
---3。
SELECT 
SCHEMA_NAME(schema_id) As 
SchemaName 
        ,name 
As TableName 
    FROM 
sys.objects
    WHERE 
type = ‘U‘
 
---4。
SELECT 
‘[‘+SCHEMA_NAME(schema_id)+‘].[‘+name+‘]‘
AS SchemaTable
FROM sys.tables
 
 
--5。顯示所有錶，并有創建和更新情況
SELECT 
*
FROM sys.Tables
GO
 
--6.
SELECT 
TABLE_NAME FROM 
INFORMATION_SCHEMA.TABLES WHERE 
TABLE_TYPE=‘BASE TABLE‘
 
--7.查指定的表的详细，字段名和字段类型
select 
*
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=‘PlatformList‘
 
--8
PRINT OBJECT_DEFINITION(OBJECT_ID(‘sys.objects‘))
IF OBJECT_ID(‘dbo.PlatformList‘, ‘U‘) IS 
NOT 
      NULL 
 
--查询表PlatformList有字段含字母P的
exec sp_columns PlatformList, @column_name = ‘P%‘
 
--9查询表PlatformList的字段详情
exec sp_columns PlatformList
--10
SELECT 
*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘PlatformList‘;
---11
EXEC sp_help PlatformList;
--12
DECLARE 
@AllTables table 
(CompleteTableName nvarchar(4000))
DECLARE 
@Search nvarchar(4000)
       ,@SQL   nvarchar(4000)
SET @Search=null 
--all rows
SET @SQL=‘select @@SERVERNAME+‘‘.‘‘+‘‘?‘‘+‘‘.‘‘+s.name+‘‘.‘‘+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id WHERE @@SERVERNAME+‘‘.‘‘+‘‘?‘‘+‘‘.‘‘+s.name+‘‘.‘‘+t.name LIKE ‘‘%‘+ISNULL(@SEARCH,‘‘)+‘%‘‘‘
 
INSERT 
INTO @AllTables (CompleteTableName)
    EXEC 
sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT 
* FROM @AllTables ORDER 
BY 1
 
--13
SELECT 
s.NAME + ‘.‘ + t.NAME AS TableName
FROM sys.tables t
INNER JOIN sys.schemas s
    ON 
t.schema_id = s.schema_id
---14
Select 
* from information_schema.columns where 
Table_name = ‘PlatformList‘
--
SELECT 
COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘PlatformList‘
 
--15
SELECT 
st.NAME, sc.NAME, sc.system_type_id
FROM sys.tables st
INNER JOIN sys.columns sc ON 
st.object_id = sc.object_id
WHERE st.name LIKE ‘%PlatformList%‘
 
--16
select
   syscolumns.name 
as [Column],
   syscolumns.xusertype as 
[Type],
   sysobjects.xtype as 
[Objtype]
from
   sysobjects, syscolumns 
where sysobjects.id = syscolumns.id
and   
sysobjects.xtype = ‘u‘
and   
sysobjects.name 
= ‘PlatformList‘
order by syscolumns.name
--17
SELECT 
* 
  FROM 
syscolumns 
 WHERE 
id=OBJECT_ID(‘PlatformList‘) 
 
--18
sp_columns @table_name=PlatformList
--19
 
select   
  syscolumns.name, 
  syscolumns.colid    
from    
  sysobjects, syscolumns  
where
  sysobjects.id = syscolumns.id and  
  sysobjects.xtype = ‘u‘ 
and  
  sysobjects.name 
= ‘PlatformList‘
order 
by syscolumns.colid 
 
--20查詢錶結構
SELECT
    c.name 
‘Column Name‘,
    t.Name 
‘Data type‘,
    c.max_length ‘Max Length‘,
    c.precision 
,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) ‘Primary Key‘
FROM   
    sys.columns c
INNER 
JOIN
    sys.types t ON 
c.user_type_id = t.user_type_id
LEFT OUTER 
      JOIN
    sys.index_columns ic ON 
ic.object_id = c.object_id AND 
ic.column_id = c.column_id
LEFT OUTER 
      JOIN
    sys.indexes i ON 
ic.object_id = i.object_id AND 
ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID(‘PlatformList‘)
 
--21数据库名PersonalCRM，表名：PersonalCRM
SELECT 
col.TABLE_CATALOG AS 
PersonalCRM
     , col.TABLE_SCHEMA AS 
Owner
     , col.TABLE_NAME AS 
TableName
     , col.COLUMN_NAME AS 
ColumnName
     , col.ORDINAL_POSITION AS 
OrdinalPosition
     , col.COLUMN_DEFAULT AS 
DefaultSetting
     , col.DATA_TYPE AS 
DataType
     , col.CHARACTER_MAXIMUM_LENGTH AS 
MaxLength
     , col.DATETIME_PRECISION AS 
DatePrecision
     , CAST(CASE 
col.IS_NULLABLE
                WHEN 
‘NO‘ THEN 
0
                ELSE 
1
            END 
AS bit)AS 
IsNullable
     , COLUMNPROPERTY(OBJECT_ID(‘[‘ 
+ col.TABLE_SCHEMA + ‘].[‘ 
+ col.TABLE_NAME + ‘]‘), col.COLUMN_NAME, ‘IsIdentity‘)AS 
IsIdentity
     , COLUMNPROPERTY(OBJECT_ID(‘[‘ 
+ col.TABLE_SCHEMA + ‘].[‘ 
+ col.TABLE_NAME + ‘]‘), col.COLUMN_NAME, ‘IsComputed‘)AS 
IsComputed
     , CAST(ISNULL(pk.is_primary_key, 0)AS 
bit)AS 
IsPrimaryKey
  FROM 
INFORMATION_SCHEMA.COLUMNS AS 
col
       LEFT 
JOIN(SELECT 
SCHEMA_NAME(o.schema_id)AS 
TABLE_SCHEMA
                      , o.name 
AS TABLE_NAME
                      , c.name 
AS COLUMN_NAME
                      , i.is_primary_key
                   FROM 
sys.indexes AS 
i JOIN sys.index_columns AS 
ic ON i.object_id = ic.object_id
                                                                     AND 
i.index_id = ic.index_id
                                         JOIN 
sys.objects AS 
o ON i.object_id = o.object_id
                                         LEFT 
JOIN sys.columns AS 
c ON ic.object_id = c.object_id
                                                                   AND 
c.column_id = ic.column_id
                  WHERE 
i.is_primary_key = 1)AS 
pk ON col.TABLE_NAME = pk.TABLE_NAME
                                                  AND 
col.TABLE_SCHEMA = pk.TABLE_SCHEMA
                                                  AND 
col.COLUMN_NAME = pk.COLUMN_NAME
 WHERE 
col.TABLE_NAME = ‘PlatformList‘
   AND 
col.TABLE_SCHEMA = ‘dbo‘
 ORDER 
BY col.TABLE_NAME, col.ORDINAL_POSITION;
--22
SELECT 
COLUMN_NAME ‘All_Columns‘ 
FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_NAME=‘PlatformList‘