sql:查询创建表的结构

?

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