sql:查询创建表的结构

--显示所有用户表：--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 tblinner join sys.schemas sch on tbl.schema_id = sch.schema_idORDER BY tbl.name---3。SELECT SCHEMA_NAME(schema_id) As SchemaName ,name As TableName FROM sys.objectsWHERE type = 'U'---4。SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'AS SchemaTableFROM sys.tables--5。顯示所有錶，并有創建和更新情況SELECT *FROM sys.TablesGO--6.SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'--7.查指定的表的详细，字段名和字段类型select *from INFORMATION_SCHEMA.COLUMNSwhere 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.COLUMNSWHERE TABLE_NAME = 'PlatformList';---11EXEC sp_help PlatformList;--12DECLARE @AllTables table (CompleteTableName nvarchar(4000))DECLARE @Search nvarchar(4000)       ,@SQL   nvarchar(4000)SET @Search=null --all rowsSET @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 @SQLSET NOCOUNT OFFSELECT * FROM @AllTables ORDER BY 1--13SELECT s.NAME + '.' + t.NAME AS TableNameFROM sys.tables tINNER 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.COLUMNSWHERE TABLE_NAME = 'PlatformList' --15SELECT st.NAME, sc.NAME, sc.system_type_idFROM sys.tables stINNER JOIN sys.columns sc ON st.object_id = sc.object_idWHERE st.name LIKE '%PlatformList%'--16select   syscolumns.name as [Column],   syscolumns.xusertype as [Type],   sysobjects.xtype as [Objtype]from    sysobjects, syscolumns where sysobjects.id = syscolumns.idand   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 cINNER JOIN     sys.types t ON c.user_type_id = t.user_type_idLEFT OUTER JOIN     sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_idLEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_idWHERE    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://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