sql:SQL Server metadata queries

http://www.mssqltips.com/sqlservertip/3449/making-sql-server-metadata-queries-easier-with-these-new-views

http://www.sql-server-helper.com/functions/metadata-functions/index.aspx

http://sqlmag.com/t-sql/partitioned-indexes-and-querying-metadata

https://github.com/rusanu/dataconnectiondialog

https://github.com/kjbartel/ConnectionDialog

https://www.codeproject.com/Articles/892233/POCO-Generator

 

?

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

--查询表结构,函数,触发器,存储过程,视图的结构,主键,外键等关系 --https://msdn.microsoft.com/en-us/library/ms187812.aspx   select * from LibrarySystem.information_schema.tables   select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from LibrarySystem.information_schema.columns where table_name = 'StaffList'   select * from LibrarySystem.information_schema.columns where table_name = 'StaffList'   select * from information_schema.columns select * from information_schema.CHECK_CONSTRAINTS select * from information_schema.COLUMN_DOMAIN_USAGE select * from information_schema.COLUMN_PRIVILEGES select * from information_schema.CONSTRAINT_COLUMN_USAGE select * from information_schema.CONSTRAINT_TABLE_USAGE select * from information_schema.DOMAIN_CONSTRAINTS select * from information_schema.DOMAINS select * from information_schema.KEY_COLUMN_USAGE select * from information_schema.PARAMETERS select * from information_schema.REFERENTIAL_CONSTRAINTS select * from information_schema.ROUTINES select * from information_schema.ROUTINE_COLUMNS select * from information_schema.SCHEMATA select * from information_schema.TABLE_CONSTRAINTS select * from information_schema.TABLE_PRIVILEGES select * from information_schema.VIEW_COLUMN_USAGE select * from information_schema.TABLES select * from information_schema.VIEW_COLUMN_USAGE select * from information_schema.VIEW_TABLE_USAGE select * from information_schema.VIEWS --Metadata queries SELECT SCHEMA_NAME(); GO --SQL SERVER 2005 metadata queries   --For User Tables select * from sys.objects where Type = 'U'   --For Views select * from sys.objects where Type = 'V'     --For Stored Procedures select * from sys.objects where Type = 'P'   --For Triggers select * from sys.objects where Type = 'TR'   --For Functions select * from sys.objects where Type = 'FN' -- select * from sys.databases select * from sys.tables select * from sys.views select * from sys.triggers select * from sys.schemas select * from sys.procedures   --for SQL 2000: SELECT * -- User tables FROM sysobjects WHERE objectproperty( id, N'IsMSShipped' ) = 0   AND objectproperty( id, N'IsTable' ) = 1 SELECT * -- Stored Procs FROM sysobjects WHERE objectproperty( id, N'IsMSShipped' ) = 0   AND objectproperty( id, N'IsProcedure' ) = 1 SELECT * -- Functions FROM sysobjects WHERE objectproperty( id, N'IsMSShipped' ) = 0   AND ( objectproperty( id, N'IsTableFunction' ) = 1      OR objectproperty( id, N'IsScalarFunction' ) = 1 ) SELECT * -- Views FROM sysobjects WHERE objectproperty( id, N'IsMSShipped' ) = 0   AND objectproperty( id, N'IsView' ) = 1 --In SQL Server 2005   SELECT * -- User tables FROM sys.objects WHERE objectproperty( object_id, N'IsMSShipped' ) = 0   AND objectproperty( object_id, N'IsTable' ) = 1   SELECT * -- Stored Procs FROM sys.objects WHERE objectproperty( object_id, N'IsMSShipped' ) = 0   AND objectproperty( object_id, N'IsProcedure' ) = 1     SELECT * -- Functions FROM sys.objects WHERE objectproperty( object_id, N'IsMSShipped' ) = 0   AND ( objectproperty( object_id, N'IsTableFunction' ) = 1      OR objectproperty( object_id, N'IsScalarFunction' ) = 1 ) SELECT * -- Views FROM sys.objects WHERE objectproperty( object_id, N'IsMSShipped' ) = 0   AND objectproperty( object_id, N'IsView' ) = 1     --   SELECT     [object_id],     inbound_count = COUNT(CASE t WHEN 'I' THEN 1 END),     outbound_count = COUNT(CASE t WHEN 'O' THEN 1 END)   FROM   (     SELECT [object_id] = referenced_object_id, t = 'I'     FROM sys.foreign_keys     UNION ALL     SELECT [object_id] = parent_object_id, t = 'O'     FROM sys.foreign_keys   ) AS c   GROUP BY [object_id];

　　

?

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 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331

--http://www.mssqltips.com/sqlservertip/3449/making-sql-server-metadata-queries-easier-with-these-new-views --http://www.sqlteam.com/article/using-metadata CREATE VIEW metadata.tables AS   SELECT     t.[object_id],     [schema] = QUOTENAME(s.name),     [table] = QUOTENAME(t.name),     [object] = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)   FROM sys.tables AS t   INNER JOIN sys.schemas AS s   ON t.[schema_id] = s.[schema_id];   --Number of rows and size of the SQL Server table CREATE FUNCTION metadata.tvf_spaceused (   @object_id INT ) RETURNS TABLE AS   RETURN   (     SELECT       [rows],       reserved_kb = r,       data_kb = p,       index_size_kb = CASE WHEN u > p THEN u - p ELSE 0 END,       unused_kb = CASE WHEN r > u THEN r - u ELSE 0 END     FROM     (       SELECT         r = (SUM (p1.reserved_page_count) + COALESCE(MAX(it.r),0)) * 8,         u = (SUM (p1.used_page_count) + COALESCE(MAX(it.u),0)) * 8,         p = (SUM (CASE WHEN p1.index_id >= 2 THEN 0 ELSE           (p1.in_row_data_page_count + p1.lob_used_page_count + p1.row_overflow_used_page_count)         END) * 8),         [rows] = SUM (CASE WHEN p1.index_id IN (0,1)           THEN p1.row_count ELSE 0 END)       FROM sys.dm_db_partition_stats AS p1       LEFT OUTER JOIN       (         SELECT it.parent_id,           r = SUM(p2.reserved_page_count),           u = SUM(p2.used_page_count)         FROM sys.internal_tables AS it         INNER JOIN sys.dm_db_partition_stats AS p2         ON it.[object_id] = p2.[object_id]         WHERE it.parent_id = @object_id         AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)         GROUP BY it.parent_id       ) AS it       ON p1.[object_id] = it.parent_id       WHERE p1.[object_id] = @object_id     ) AS x); GO   -- SELECT   -- basic metadata   t.[object_id],   t.[schema],   t.[table],   t.[object],     -- mimic spaceused   su.[rows],   su.reserved_kb,   su.data_kb,   su.index_size_kb,   su.unused_kb FROM metadata.tables AS t CROSS APPLY metadata.tvf_spaceused(t.[object_id]) AS su ORDER BY t.[object]; --Last SQL Server table accessed CREATE VIEW metadata.table_access AS   SELECT     [object_id],     last_read = MAX(last_read),     last_write = MAX(last_write)   FROM   (     SELECT [object_id],       last_read = (SELECT MAX(d) FROM (VALUES         (last_user_seek),(last_user_scan),(last_user_lookup))          AS reads(d)),       last_write = (SELECT MAX(d) FROM (VALUES         (last_user_update))          AS writes(d))     FROM sys.dm_db_index_usage_stats   ) AS x GROUP BY [object_id];   --All kinds of data about SQL Server columns CREATE VIEW metadata.columns AS   SELECT     c.[object_id],     column_count = COUNT(c.column_id),     column_list = STUFF((SELECT N',' + QUOTENAME(name)       FROM sys.columns AS c2       WHERE c2.[object_id] = c.[object_id]       ORDER BY c2.column_id       FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,N''),     has_identity_column = COUNT(NULLIF(c.is_identity,0)),     computed_column_count = COUNT(NULLIF(c.is_computed,0)),     persisted_computed_column_count = COUNT(NULLIF(cc.is_persisted,0)),     LOB_column_count = COUNT     (       CASE         WHEN c.system_type_id IN (34,35,99,241) THEN 1         WHEN c.system_type_id IN (165,167,231) AND c.max_length = -1 THEN 1       END     ),     XML_column_count = COUNT(CASE WHEN c.system_type_id = 241 THEN 1 END),     spatial_column_count = COUNT(CASE WHEN c.user_type_id IN (129,130) THEN 1 END),     hierarchyid_column_count = COUNT(CASE WHEN c.user_type_id = 128 THEN 1 END),     rowversion_column_count = COUNT(CASE WHEN c.system_type_id = 189 THEN 1 END),     GUID_column_count = COUNT(CASE WHEN c.system_type_id = 36 THEN 1 END),     deprecated_column_count = COUNT(CASE WHEN c.system_type_id IN (34,35,99) THEN 1 END),     alias_type_count = COUNT(NULLIF(t.is_user_defined,0))   FROM sys.columns AS c   INNER JOIN sys.types AS t   ON c.system_type_id = t.system_type_id   LEFT OUTER JOIN sys.computed_columns AS cc   ON c.[object_id] = cc.[object_id]   AND c.column_id = cc.column_id   GROUP BY c.[object_id]; --All kinds of data about SQL Server indexes CREATE VIEW metadata.indexes AS   SELECT     i.[object_id],     i.has_clustered_index,     i.has_primary_key,     i.unique_index_count,     i.filtered_index_count,     p.nonclustered_index_count,     p.xml_index_count,     p.spatial_index_count   FROM   (     SELECT [object_id],       has_clustered_index = MIN(index_id),       has_primary_key = MAX(CONVERT(TINYINT,is_primary_key)),       unique_index_count = COUNT(CASE WHEN is_unique = 1 THEN 1 END),       filtered_index_count = COUNT(CASE WHEN has_filter = 1 THEN 1 END)     FROM sys.indexes AS i     GROUP BY i.[object_id]   ) AS i   LEFT OUTER JOIN   (     SELECT [object_id],       nonclustered_index_count = COALESCE([2],0),       xml_index_count = COALESCE([3],0),       spatial_index_count = COALESCE([4],0)       -- columnstore, hash etc. too if you like     FROM     (       SELECT [object_id], [type], c = COUNT(*)       FROM sys.indexes       GROUP BY [object_id], [type]     ) AS x     PIVOT (MAX(c) FOR type IN ([2],[3],[4],[5],[6],[7])) AS p   ) AS p   ON i.[object_id] = p.[object_id];   --Number of default and check SQL Server constraints CREATE VIEW metadata.constraint_counts AS   SELECT     [object_id] = parent_object_id,     default_constraint_count = COUNT(CASE t WHEN 'D' THEN 1 END),     check_constraint_count = COUNT(CASE t WHEN 'C' THEN 1 END)   FROM   (     SELECT parent_object_id, t = 'D'     FROM sys.default_constraints     UNION ALL     SELECT parent_object_id, t = 'C'     FROM sys.check_constraints   ) AS c   GROUP BY parent_object_id;   --Number of after and instead of SQL Server triggers CREATE VIEW metadata.trigger_counts AS   SELECT     [object_id] = parent_id,     after_trigger_count = COUNT(NULLIF(is_instead_of_trigger,1)),     instead_of_trigger_count = COUNT(NULLIF(is_instead_of_trigger,0))   FROM sys.triggers   GROUP BY parent_id;   --SQL Server compression and partition information CREATE VIEW metadata.basic_storage AS   SELECT     [object_id],     [data_compression], -- on at least one partition     has_partitions = CASE partition_count WHEN 1 THEN 0 ELSE 1 END,     partition_count   FROM   (     SELECT         [object_id],       [data_compression] = MAX(COALESCE(NULLIF(data_compression_desc,'NONE'),'')),       partition_count = COUNT(*)     FROM sys.partitions     WHERE index_id IN (0,1)     GROUP BY [object_id]   ) AS p;   --CREATE VIEW metadata.foreign_key_counts AS   SELECT     [object_id],     inbound_count = COUNT(CASE t WHEN 'I' THEN 1 END),     outbound_count = COUNT(CASE t WHEN 'O' THEN 1 END)   FROM   (     SELECT [object_id] = referenced_object_id, t = 'I'     FROM sys.foreign_keys     UNION ALL     SELECT [object_id] = parent_object_id, t = 'O'     FROM sys.foreign_keys   ) AS c   GROUP BY [object_id];   --Number of SQL Server schema-bound references CREATE VIEW metadata.schemabound_references AS   SELECT     t.[object_id],     reference_count = COUNT(*)   FROM metadata.tables AS t   CROSS APPLY sys.dm_sql_referencing_entities(t.[object], N'OBJECT') AS r   WHERE EXISTS   (     SELECT 1     FROM sys.sql_modules AS m     WHERE m.[object_id] = r.referencing_id     AND m.is_schema_bound = 1   )   GROUP BY t.[object_id];   --Pulling it all together CREATE VIEW metadata.uber_table_info AS   SELECT     -- basic metadata     t.[object_id],     t.[schema],     t.[table],     t.[object],         -- mimic spaceused     su.[rows],     su.reserved_kb,     su.data_kb,     su.index_size_kb,     su.unused_kb,         -- last access:     ta.last_read,     ta.last_write,         -- column info     c.column_count,     c.column_list,     c.has_identity_column,     c.computed_column_count,     c.persisted_computed_column_count,     c.LOB_column_count,     c.XML_column_count,     c.spatial_column_count,     c.hierarchyid_column_count,     c.rowversion_column_count,     c.GUID_column_count,     c.deprecated_column_count,     c.alias_type_count,         -- index info     i.has_clustered_index,     i.has_primary_key,     i.nonclustered_index_count,     i.unique_index_count,     i.filtered_index_count,     i.xml_index_count,     i.spatial_index_count,         -- constraint info     default_constraint_count = COALESCE(cc.default_constraint_count,0),     check_constraint_count = COALESCE(cc.check_constraint_count,0),         -- trigger info     after_trigger_count = COALESCE(tr.after_trigger_count,0),     instead_of_trigger_count = COALESCE(tr.instead_of_trigger_count,0),         -- storage info     st.[data_compression],     st.has_partitions,     st.partition_count,         -- foreign key counts - inbound, outbound     inbound_fk_count = COALESCE(fk.inbound_count,0),     outbound_fk_count = COALESCE(fk.outbound_count,0),         -- schema-bound references:     schemabound_references = COALESCE(sb.reference_count,0)       FROM metadata.tables AS t   CROSS     APPLY metadata.tvf_spaceused(t.[object_id]) AS su   LEFT OUTER JOIN metadata.table_access AS ta   ON t.[object_id] = ta.[object_id]   INNER JOIN metadata.columns AS c   ON t.[object_id] = c.[object_id]   LEFT OUTER JOIN metadata.indexes AS i   ON t.[object_id] = i.[object_id]   LEFT OUTER JOIN metadata.constraint_counts AS cc   ON t.[object_id] = cc.[object_id]   LEFT OUTER JOIN metadata.trigger_counts AS tr   ON t.[object_id] = tr.[object_id]   LEFT OUTER JOIN metadata.basic_storage AS st   ON t.[object_id] = st.[object_id]   LEFT OUTER JOIN metadata.foreign_key_counts AS fk   ON t.[object_id] = fk.[object_id]   LEFT OUTER JOIN metadata.schemabound_references AS sb   ON t.[object_id] = sb.[object_id];   --Performance SELECT * FROM metadata.uber_table_info ORDER BY [object];

　　

创建带有 FOREIGN KEY的数据表:

SQL Server / Oracle / MS Access

?

1 2 3 4 5 6

CREATE TABLE Project (     ProjectID int NOT NULL PRIMARY KEY,     ProjectName varchar(100) NOT NULL,     ProjectManagerId int FOREIGN KEY REFERENCES Employee(EmployeeId) )

　My SQL

?

1 2 3 4 5 6 7 8

CREATE TABLE Project (     ProjectId int NOT NULL,     ProjectName varchar(100) NOT NULL,     ProjectManagerId int,     PRIMARY KEY (ProjectId ),     FOREIGN KEY (ProjectManagerId) REFERENCES Employee(EmployeeId) )

　

如果你想给 foreign key 约束取一个名字：
MySQL / SQL Server / Oracle / MS Access:

?

1 2 3 4 5 6 7 8 9

CREATE TABLE Project (     ProjectId int NOT NULL,     ProjectName varchar(100) NOT NULL,     ProjectManagerId int,     PRIMARY KEY (ProjectId ),     CONSTRAINT fk_EmployeeId FOREIGN KEY (ProjectManagerId )     REFERENCES Employee(EmployeeId) )

　

使用ALTER TABLE设置 FOREIGN KEY :

SQL Server / MySQL / Oracle / MS Access:

?

1 2 3

ALTER TABLE Project ADD FOREIGN KEY (ProjectManagerId ) REFERENCES Employee(EmployeeId)

　

删除 PRIMARY KEY:

SQL Server / Oracle / MS Access:

?

1 2	ALTER TABLE Project  DROP CONSTRAINT fk_EmployeeId

　　My SQL:

?

1 2	ALTER TABLE Orders DROP FOREIGN KEY fk_EmployeeId

　　https://msdn.microsoft.com/en-us/library/ms189049.aspx

https://technet.microsoft.com/en-us/library/ms189807(v=sql.105).aspx

　

　

?

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

--http://www.infobarrel.com/15_Sql_Server_Queries --1. Find Host name, Edition and Version number SELECT SERVERPROPERTY('MachineName') as Host, SERVERPROPERTY('InstanceName') as Instance, @@VERSION as "Version Number", SERVERPROPERTY('Edition') as Edition, SERVERPROPERTY('ProductLevel') as ProductLevel, Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else 'STAND ALONE' end as "Server Type" --2. Find number of Tables in Database SELECT COUNT(OBJ.ID) as "Table Count" FROM SYSOBJECTS OBJ WHERE XTYPE='U' --3. Find number of Stored Procedures in Database SELECT COUNT(OBJ.ID) as "SP Count" FROM SYSOBJECTS OBJ WHERE XTYPE='P' --4. Retrieve list of Tables with its row count SELECT OBJ.NAME as "Table Name", MAX(SI.ROWS) as "Row Count" FROM SYSOBJECTS OBJ, SYSINDEXES SI WHERE OBJ.XTYPE = 'U' AND SI.ID = OBJECT_ID(OBJ.NAME) GROUP BY OBJ.NAME ORDER BY 2 DESC --5. Find Created and Modified Date for Tables SELECT NAME as "Table Name", CREATE_DATE as "Created Date", MODIFY_DATE as "Modified Date" FROM SYS.OBJECTS WHERE TYPE = 'U' --AND NAME LIKE '%sys_%' ORDER BY 3 DESC --6. Find Created and Modified Date for Stored Procedures SELECT NAME as "SP Name", CREATE_DATE as "Created Date", MODIFY_DATE as "Modified Date" FROM SYS.OBJECTS WHERE TYPE = 'P' --AND NAME LIKE '%delete%' ORDER BY 3 DESC --7. Find list of Tables containing a Column Name SELECT s.[NAME] 'Schema', t.[NAME] 'Table', c.[NAME] 'Column', d.[NAME] 'Data Type', d.[MAX_LENGTH] 'Max Length', c.[IS_IDENTITY] 'Is Id', c.[IS_NULLABLE] 'Is Nullable', t.[MODIFY_DATE] 'Date Modified', t.[CREATE_DATE] 'Date created' FROM SYS.SCHEMAS s INNER JOIN SYS.TABLES t ON s.SCHEMA_ID= t.SCHEMA_ID INNER JOIN SYS.COLUMNS c ON t.OBJECT_ID= c.OBJECT_ID INNER JOIN SYS.TYPES d ON c.USER_TYPE_ID= d.USER_TYPE_ID WHERE c.NAME like '%backup%' --8. Find list of Objects containing a Text SELECT o.NAME FROM SYSCOMMENTS (nolock) AS c INNER JOIN SYSOBJECTS (nolock) AS o ON c.ID = o.id WHERE c.TEXT LIKE '%BACKUP%'; --9. Find physical location of Database file SELECT DB_NAME(DATABASE_ID) as DatabaseName, NAME, TYPE_DESC, PHYSICAL_NAME FROM SYS.MASTER_FILES --10. Find the login with admin level roles SELECT l.NAME, l.DENYLOGIN, l.ISNTNAME, l.ISNTGROUP, l.ISNTUSER FROM MASTER.DBO.SYSLOGINS l WHERE l.SYSADMIN = 1 OR l.SECURITYADMIN = 1 --11. Find last Database backup date SELECT db.NAME, CASE WHEN MAX(b.backup_finish_date) IS NULL THEN 'No Backup' ELSE convert(varchar(100), MAX(b.BACKUP_FINISH_DATE)) END AS last_backup_finish_date FROM SYS.DATABASES db LEFT OUTER JOIN MSDB.DBO.BACKUPSET b ON db.NAME = b.DATABASE_NAME AND b.TYPE = 'D' WHERE db.DATABASE_ID NOT IN (2) GROUP BY db.NAME ORDER BY 2 DESC --12. Find most intensively read queries in Database SELECT TOP 50 SUBSTRING(QT.TEXT, (QS.STATEMENT_START_OFFSET/2) +1, ((CASE QS.STATEMENT_END_OFFSET WHEN -1 THEN (QT.TEXT) ELSE QS.STATEMENT_END_OFFSET END - QS.STATEMENT_START_OFFSET)/2)+1), QS.EXECUTION_COUNT, QS.TOTAL_LOGICAL_READS, QS.TOTAL_ELAPSED_TIME, QS.LAST_ELAPSED_TIME, QS.MIN_ELAPSED_TIME, QS.MAX_ELAPSED_TIME, QS.LAST_EXECUTION_TIME, QP.QUERY_PLAN FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) QT CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(QS.PLAN_HANDLE) QP WHERE QT.ENCRYPTED=0 ORDER BY QS.TOTAL_LOGICAL_READS DESC, QS.TOTAL_ELAPSED_TIME DESC   --13. Find current users, blocked process, memory, etc. sp_who2 --14. Find list of index for Tables SELECT A.NAME TABLE_NAME, B.NAME INDEX_NAME, D.NAME COLUMN_NAME FROM SYS.TABLES A, SYS.INDEXES B, SYS.INDEX_COLUMNS C, SYS.COLUMNS D WHERE A.OBJECT_ID = B.OBJECT_ID AND B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID AND C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID --AND A.NAME LIKE '%BACKUP%'; --15. Find Database size and Log file size WITH FS AS (SELECT DATABASE_ID, TYPE, SIZE * 8.0 / 1024 SIZE FROM SYS.MASTER_FILES) SELECT NAME, (SELECT SUM(SIZE) FROM FS WHERE TYPE = 0 AND FS.DATABASE_ID = DB.DATABASE_ID) DATAFILESIZEMB, (SELECT SUM(SIZE) FROM FS WHERE TYPE = 1 AND FS.DATABASE_ID = DB.DATABASE_ID) LOGFILESIZEMB FROM SYS.DATABASES DB

　　

 查询外键相关的两个表和外键的列

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT     OBJECT_NAME(f.parent_object_id) as tbl,     OBJECT_NAME(fc.referenced_object_id) as refTable,     COL_NAME(f.parent_object_id, fc.parent_column_id) as foreignKeyColumnName,     COL_NAME(f.referenced_object_id,fc.referenced_column_id ) as foreignKeyParentColumnName FROM sys.foreign_keys f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id WHERE f.parent_object_id = OBJECT_ID('dbo.ProjectList')     select * from sys.foreign_keys select * from sys.foreign_key_columns   SELECT OBJECT_NAME(parent_object_id) from sys.foreign_keys SELECT OBJECT_NAME(referenced_object_id) from sys.foreign_key_columns