select name,modify_date from sys.all_objects where type='P' order by modify_date desc

select name,modify_date from sys.all_objects where type='TR' order by modify_date desc

select name,modify_date from sys.all_objects where type='u' order by modify_date desc

select user_type_id,name,is_table_type from sys.types where is_user_defined=1 order by user_type_id desc

?

?--sql語句查詢一個表的列數

select count(name) from syscolumns where id=(select id from sysobjects where xtype='u' and name='Bms_Tasks')

?

SELECT sys_o.name as TableName,sys_o.name as ColumnName
From syscolumns sys_c INNER JOIN sysobjects sys_o
ON sys_c.id=sys_o.id
AND sys_o.type='U'
AND sys_c.name=' 你所需要查找的字段名字 '

?

select * from (SELECT TOP 100
--創(chuàng)建時間
QS.creation_time,
--查詢語句
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
) AS statement_text,
--執(zhí)行文本
ST.text,
--執(zhí)行計劃
QS.total_worker_time,QS.last_worker_time,QS.max_worker_time,QS.min_worker_time

FROM sys.dm_exec_query_stats QS
--關鍵字
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST ) My_Table
WHERE My_Table.creation_time BETWEEN '2013-8-21 16:00:00' AND '2013-10-20 17:00:00'
AND My_Table.statement_text LIKE '%%'
ORDER BY My_Table.creation_time DESC

?

?

查某一列(或多列)的重復值 (只可以查出重復記錄的值，不能查出整個記錄的信息)

例如:查找stuid，stuname重復的記錄

select stuid，stuname from stuinfo

group by stuid，stuname

having(count(*))>1

?

-- 在SQL查詢分析器里運行查找其中一個數據庫內的所有表的行數

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#T') and type='U')
drop table #T

CREATE TABLE #T ([name] nvarchar(255),rows bigint)
DECLARE @Name nvarchar(255),@Sql nvarchar(4000)
DECLARE TName CURSOR FOR
SELECT [name] FROM sysobjects WHERE xtype = 'U'
OPEN TName
FETCH NEXT FROM TName
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql='INSERT INTO #T ([name],rows) SELECT '''+@name+''' AS name, COUNT(1) AS row FROM '+@name
EXEC(@Sql)
FETCH NEXT FROM TName INTO @name
END
CLOSE TName
DEALLOCATE TName
SELECT * FROM #T

?

--根據已知的主鍵查詢所有引用這個主鍵的外鍵約束的名稱

SELECT OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName
, OBJECT_NAME(parent_object_id) AS TableName ,Name FROM sys.foreign_keys
WHERE referenced_object_id=OBJECT_ID('dbo.Bms_JobTicket')

SQL 查詢 信息