--显示所有用户表：

--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'