sql:查询创建表的结构
时间:2022-03-10 17:12
--显示所有用户表: --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
sql:查询创建表的结构,布布扣,bubuko.com