sql2000与sql2005的一些语法区别

ID	Object Type	SQL Server 2000	SQL Server 2005
1	Data Models	Table = dtproperties SELECT * FROM dbo.dtproperties GO	Table = dbo.sysdiagrams SELECT * FROM dbo.sysdiagrams; GO
2	Tables	Table = sysobjects SELECT * FROM dbo.sysobjects WHERE xtype = 'u' ORDER BY Name GO	Table = sys.tables SELECT * FROM sys.tables ORDER BY Name; GO
3	Columns	Table = syscolumns SELECT o.name, c.name FROM dbo.syscolumns c INNER JOIN dbo.sysobjects o ON c.id = o.id WHERE o.name = 'MyTableName' ORDER BY c.colorder GO	Table = sys.all_columns SELECT OBJECT_NAME([Object_ID]) AS 'TableName', [Name] AS 'ColumnName', Column_ID FROM sys.all_columns ORDER BY TableName, Column_ID; GO
4	Primary Keys	Table = sysobjects SELECT p.name, OBJECT_NAME(parent_obj) AS 'Table Name' FROM dbo.sysobjects p WHERE p.xtype = 'PK' ORDER BY p.Name GO	Table = sys.objects SELECT OBJECT_NAME(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey' FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.Type = 'PK' ORDER BY o.Name; GO
5	Foreign Keys	Table = sysforeignkeys SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS 'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.[name] AS 'PKColumnName' FROM sysforeignkeys f INNER JOIN syscolumns c1 ON f.fkeyid = c1.[id] AND f.fkey = c1.colid INNER JOIN syscolumns c2 ON f.rkeyid = c2.[id] AND f.rkey = c2.colid ORDER BY OBJECT_NAME(f.rkeyid) GO	Table = sys.foreign_key_columns SELECT OBJECT_NAME(f.constraint_object_id) AS 'ForeignKey', OBJECT_NAME(f.parent_object_id) AS 'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.referenced_object_id) AS 'PKTable', c2.[name] AS 'PKColumnName' FROM sys.foreign_key_columns f INNER JOIN sys.all_columns c1 ON f.parent_object_id = c1.[object_id] AND f.parent_column_id = c1.column_id INNER JOIN sys.all_columns c2 ON f.referenced_object_id = c2.[object_id] AND f.referenced_column_id = c2.column_id ORDER BY OBJECT_NAME(f.referenced_object_id); GO
6	Constraints	Table = sysconstraints SELECT o.[name] AS 'DefaultName', OBJECT_NAME(c.[id]) AS 'TableName', col.[name] AS 'ColumnName' FROM dbo.sysconstraints c INNER JOIN dbo.sysobjects o ON c.constid = o.[id] INNER JOIN dbo.syscolumns col ON col.[id] = c.colid ORDER BY o.[name] GO	Table = sys.objects SELECT OBJECT_NAME(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey' FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.Type IN ('C', 'D', 'UQ') ORDER BY o.Name; GO
7	FileGroups/Partitions	Table = sysfilegroups SELECT * FROM sysfilegroups GO	Table = sys.data_spaces SELECT * FROM sys.data_spaces; GO
8	Stored Procedures	Table = sysobjects SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype = 'p' ORDER BY o.[Name] GO	Table =  sys.objects SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] = 'p' ORDER BY o.[Name]; GO
9	Functions	Table = sysobjects SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype IN ('fn', 'if', 'tf') ORDER BY o.[Name] GO	Table = sys.objects SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] IN ('fn', 'fs', 'ft', 'if', 'tf')ORDER BY o.[Name]; GO
10	Views	Table = sysobjects SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype = 'v' ORDER BY o.[Name] GO	Table = sys.objects SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] = 'V' ORDER BY o.[Name]; GO