首页 > 代码库 > sql:查询创建表的结构

sql:查询创建表的结构

2024-07-04 16:53:07   231人阅读

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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‘

http://stackoverflow.com/questions/2418527/sql-server-query-to-get-the-list-of-columns-in-a-table-along-with-data-types-no

http://stackoverflow.com/questions/1054984/get-columns-of-a-table-sql-server


联系
我们