|
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
|
--显示所有用户表：--1SELECT 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‘
--8PRINT 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
--10SELECT
*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘PlatformList‘;
---11EXEC sp_help PlatformList;
--12DECLARE
@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
--13SELECT
s.NAME + ‘.‘ + t.NAME AS TableName
FROM sys.tables t
INNER JOIN sys.schemas s
ON
t.schema_id = s.schema_id
---14Select
* from information_schema.columns where
Table_name = ‘PlatformList‘
--SELECT
COLUMN_NAME,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘PlatformList‘
--15SELECT
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%‘
--16select 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
--17SELECT
*
FROM
syscolumns
WHERE
id=OBJECT_ID(‘PlatformList‘)
--18sp_columns @table_name=PlatformList--19select 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，表名：PersonalCRMSELECT
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;
--22SELECT
COLUMN_NAME ‘All_Columns‘
FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME=‘PlatformList‘
|
http://*.com/questions/1054984/get-columns-of-a-table-sql-server