Summary of some common SQL Server knowledge

I usually work in Oracle for a while, and I am confused about SQL Server. In order to avoid further blurring, I took some time today to sort out some of my commonly used SQL Server and send it out, I forgot to come and try again in a few days. There is no technical content.

1. SQLserver forgot password modification method

Connect to SQL Server with "Windows Authentication" from "query analyzer" and execute the following process:

EXEC sp_password NULL, ''Your new password '', ''username, for example, sa''

2. Change the owner of objects in the current database.

EXEC sp_changeobjectowner ''dbo. Object name, for example, table name'' and ''new owner''

3. Distributed Query between SQLSERVER and SQLSERVER

Establish a connection server
Exec sp_addmediaserver ''testlink '', '''', '''sqloledb'', ''ip address of the remote database''

Create a ing between remote logins on the linked server
Exec sp_add1_srvlogin ''testlink '', ''false'', null, ''S'', ''password''

Query example
Select * from TESTLINK. Database name. dbo. Table name

4. View all tables in the database

Create view dbo. ALL_TABLES
AS
SELECT top 100 PERCENT a. ID,
Case when a. colorder = 1 THEN d. name ELSE ''' end as TableName,
Case when a. colorder = 1 THEN isnull (f. value, ''') ELSE ''' end as table description,
A. colorder AS field No., a. name AS field name,
ISNULL (g. [value], ''') AS field description, case when columnproperty (a. id,
A. name, ''isidentifi'') = 1 then'' & radic; ''else' ''' end as id,
CASE WHEN EXISTS
(SELECT 1
FROM dbo. sysindexes si INNER JOIN
Dbo. sysindexkeys sik ON si. id = sik. id AND si. indid = sik. indid INNER JOIN
Dbo. syscolumns SC ON SC. id = sik. id AND SC. colid = sik. colid INNER JOIN
Dbo. sysobjects so ON so. name = si. name AND so. xtype = ''pk''
WHERE SC. id = a. id AND SC. colid = a. colid) then'' & radic; ''else' ''' END AS primary key,
B. name AS type, a. length AS length, COLUMNPROPERTY (a. id, a. name, ''precision '')
AS precision, ISNULL (COLUMNPROPERTY (a. id, a. name, ''scal''), 0) AS decimal places,</