--主键表
SELECT TOP 50 * 
FROM sys.key_constraints

--没有主键的表
SELECT TOP 50 * 
FROM sys.tables a
WHERE a.object_id NOT IN(
	SELECT parent_object_id
	FROM sys.key_constraints
)
    
    
	SELECT TOP 50 * 
	FROM sys.parameters 

--查询计算列
SELECT TOP 50 * 
FROM sys.computed_columns 

Select  Object_Name(id.object_id) As [table_name]
    , id.name As [column_name]
    , t.name As [data_type]
	,seed_value
	,increment_value
    , Cast(id.last_value As bigint) As [last_value]
    , Case 
        When t.name = 'tinyint'   Then 255 
        When t.name = 'smallint'  Then 32767 
        When t.name = 'int'       Then 2147483647 
        When t.name = 'bigint'    Then 9223372036854775807
        End As [max_value]
From sys.identity_columns As id
Join sys.types As t
    On id.system_type_id = t.system_type_id
Where id.last_value Is Not NULL
ORDER BY last_value DESC

In this blog I would like to demonstrate a scenario where users want to move the changes between the tables in two different databases.
Let’s say we would like to compare and move the changes between the databases for some tables using T-SQL
The below example talks about moving the changes between the two databases in the same instance However the same can be extended across instances if you use linked server or SSIS packages.
Also we can write queries to move the DML changes from source to destination and vice versa. Let’s look at the below example
 
 
--creating a source database
create database source
 
--create source  table
use source
 
create table Product(
 
Pid int  primary key ,
Pname varchar (10),
Pcost float,
source int ,
location varchar(10))
 
--create destination database
 
create database Destination
 
--create destination table
 
use Destination
 
create table Product(
 
Pid int  primary key ,
Pname varchar (10),
Pcost float,
source int,
location varchar(10) )
 
--Insert data into source table
use source
 
insert into product values  ( 1,'rdbms',100,200,'ind')
insert into product values  ( 2,'dbm',20,100,'US')
insert into product values  ( 3,'arp',30,250,'UK')
insert into product values  ( 4,'mqr',40,100,'ind')
insert into product values  ( 5,'ttp',50,200,'us')
 
-- EXCEPT returns any distinct values from the left query that are not also  found on the right query.
--The below query gives us difference between sourec and destination
-- we can use except ket word to look at selected columns or entire table
 
select * from source.dbo.product
 
except
 
select * from [Destination].dbo.product
 
--updating destination table with the changes from source
 
insert into [Destination].dbo.product
select * from source.dbo.product
except
select * from [Destination].dbo.product
 
-- We see that the destination is populated with all the rows from source
 
select * from [Destination].dbo.product
 
--Now lets update the row in the source and see how it works
 
update source.dbo.product
set pname='sql'
where pid =1
--run the below query
select * from source.dbo.product
 
except
 
select * from [Destination].dbo.product
 
-- the result gives us the only row which was changed in source
 
-- loading the deiffrences to a temp table
select * into #temp from source.dbo.product
 
except
 
select * from [Destination].dbo.product
 
--updating the destination with changes
 
update [Destination].dbo.product
set [Destination].dbo.product.pname= #temp.pname
from #temp where #temp.pid= [Destination].dbo.product.pid
 
--lets run the statement to see the difference between these tables
 
select * from source.dbo.product
 
except
 
select * from [Destination].dbo.product
 
--lets see how the delete works
 
delete from source.dbo.product where pid= 2
 
-- to see the rows which were deleted at source or inserted at destination only
select * from [Destination].dbo.product
except
select * from source.dbo.product
--based on the application logic either we will insert it back in the source or delete from dest
 
--lets say we want to delete from dest as well ,
 
select * into  #temp from [Destination].dbo.product
except
select * from source.dbo.product
 
delete from [Destination].dbo.product where pid in ( select pid from #temp)
 
-- Now lets see that difference between the tables
select * from [Destination].dbo.product
except
select * from source.dbo.product
 

来自:How to compare the rows of two tables and fetch the differential data.

场景：从发布库上将一数据库移到另一服务器，在对表改名时提示“该表已为了复制而被发布，所以无法重命名。”
原因：移的数据库原来参与了复制分发，需要在新服务器上去掉。
方法：sp_removedbreplication 'mydb'

sp_removedbreplication
该存储过程在发布服务器的发布数据库中或在订阅服务器的订阅数据库中执行。 该过程将从执行它的数据库中删除所有复制对象，但它不会从其他数据库（例如，分发数据库）中删除对象
只有当其他删除复制对象的方法都失败后，才应当使用此过程。

在设置数据库状态时
with子句忽略，则当数据库中存在任何锁时，ALTER DATABASE 语句将无限期等待。
with NO_WAIT ，指定若有事务还没有提交，则立即失败，不再进行下面的操作
with ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE:指定是在指定秒数之后回滚还是立即回滚。

注意：并非所有数据库选项都使用 WITH <termination> 子句，也不是所有数据库选项都能结合其他选项指定。

ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

ALTER DATABASE SET 选项 (Transact-SQL)

【场景】：
对象浏览器中只有数据库名，数据库展不开，查看日志【由于数据库没有完全关闭，无法重新生成日志。】，推测原因是服务器异常关停，造成数据库没有正常写完数据造成。

【处理方法】：修复数据，舍弃异常数据。
DBCC CHECKDB (mydb, REPAIR_ALLOW_DATA_LOSS),前提是要在单用户下

【完整的处理方法】：

ALTER DATABASE [mydb] SET EMERGENCY 
--EMERGENCY状态下 数据库标记为 READ_ONLY，并禁用日志记录，并且仅限 sysadmin 固定服务器角色的成员进行访问。 EMERGENCY 主要用于故障排除。
ALTER DATABASE [mydb] SET SINGLE_USER
DBCC CHECKDB (mydb, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [mydb] SET MULTI_USER

【注】
若以上执行不成功，按以下方式处理：
1，数据库mydb脱机
2，将mdf文件copy到新位置
3，删除原数据库mydb
4，建立新库mydb
5, 脱机mydb
6, 将备份的mdf文件覆盖新库
7，联机新库，此时是置疑状态
8，执行【完整的处理方法】