sql server query to get the list of column name in a table

?

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

--SQL Server 2005, 2008 or 2012:   SELECT * FROM information_schema.tables --SQL Server 2000:   SELECT * FROM sysobjects WHERE xtype='U'   SELECT * FROM sysobjects WHERE xtype='U'     SELECT TABLE_NAME FROM geovidnu.INFORMATION_SCHEMA.Tables   SELECT sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = 'U'     EXEC sp_spaceused 'YourTableName'   --顯示所有錶的列名 SELECT COLUMN_NAME,TABLE_NAME FROM geovidnu.INFORMATION_SCHEMA.COLUMNS   SELECT *  FROM geovidnu.INFORMATION_SCHEMA.COLUMNS         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('YourTableName')   --http://stackoverflow.com/questions/2418527/sql-server-query-to-get-the-list-of-columns-in-a-table-along-with-data-types-no   select COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,        NUMERIC_PRECISION, DATETIME_PRECISION,        IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='YourTableName'   Select C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_PRECISION, C.IS_NULLABLE, TC.CONSTRAINT_NAME From INFORMATION_SCHEMA.COLUMNS As C     Left Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As TC       On TC.TABLE_SCHEMA = C.TABLE_SCHEMA           And TC.TABLE_NAME = C.TABLE_NAME           And TC.CONSTRAINT_TYPE = 'PRIMARY KEY' Where C.TABLE_NAME = 'YourTableName'   -- --http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

--比較錶結構 drop table #a go drop table #b go SELECT COLUMN_NAME into #a FROM geovidnu.INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME='('YourTableName')' SELECT COLUMN_NAME into #b geovidnu1.INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME='('YourTableName')'     select * from #b where COLUMN_NAME in (select COLUMN_NAME from #a) select * from #a where COLUMN_NAME in (select COLUMN_NAME from #b)   select * from #a where COLUMN_NAME not in (select COLUMN_NAME from #b) select * from #b where COLUMN_NAME not in (select COLUMN_NAME from #a)   select * from #a a, #b b where a.COLUMN_NAME=b.COLUMN_NAME     select * from #a a left join #b b on a.COLUMN_NAME=b.COLUMN_NAME select * from #b a left join #a b on a.COLUMN_NAME=b.COLUMN_NAME