Obtain the full path of database files using SQL statements

Obtain the full path of database files using SQL statements

Select filename from master. dbo. sysdatabases where name = @ databasename

Some useful system stored procedures and usage
---------------------------
Obtain the SQL SERVER name.
Select convert (sysname, serverproperty (N 'servername '))

Read key value
Xp_instance_regread N 'HKEY _ LOCAL_MACHINE ', n' SOFTWARE \ Microsoft \ MSSQLServer \ setup', n' sqlpath'

Obtain SQL SERVER platform information
Xp_msver N 'productversion', n' Language ', n' Platform', n' windowsversion', n' ProcessorCount ', n' PhysicalMemory'

Obtain the logon mode of the SQL SERVER instance.
Xp_instance_regread N 'HKEY _ LOCAL_MACHINE ', n' SOFTWARE \ Microsoft \ MSSQLServer', 'loginmode'
LoginMode = 2 indicates Hybrid Authentication = 1 default nt authentication = 0 sa Authentication
The login security mode; 0 indicates Normal, 1 indicates Windows NT Integrated, and 2 indicates Mixed.

Modify the logon mode of an SQL SERVER instance
Xp_instance_regwrite N 'HKEY _ LOCAL_MACHINE ', n' SOFTWARE \ Microsoft \ MSSQLServer', 'loginmode', n' REG _ dword', 1
1 --- Windows Authentication Mode
2---SQL and Windows authentication modes

Obtain the SQL server name and domain name list.
Xp_ntsec_enumdomains

Exec sp_grantdbaccess N 'zhang ', N 'zhang'
Exec sp_droplogin N 'zhang'
Exec sp_revokedbaccess N 'zhang'
Exec sp_dbcmptlevel N 'dbname'

Sp_stored_procedures
Get the Stored Procedure List

Xp_availablemedia 2
Obtain hard disk partition information

EXECUTE master. dbo. xp_dirtree N 'e: \ ', 1, 1
Obtain the file list under E: \.

EXECUTE master. dbo. xp_fileexist N 'f: \ Program Files \ Microsoft SQL Server \ MSSQL \ BACKUP \ fdsa. dat'
Whether the file exists

Backup log database_name with NO_LOG | TRUNCATE_ONLY
Truncation transaction logs

Dbcc shrinkdatabase database_name
Shrink Database

Exec sp_addumpdevice N 'disk', n'bakdevic', n'd: \ BACKUP \ bakdevic'
Add backup device
Exec sp_dropdevice N 'bakdevic'
Delete a backup device

Xp_instance_regread N 'HKEY _ CURRENT_USER ', n' Software \ Microsoft \ MSSQLServer', n' lastbackupfiledir'
Path of the last backup

Xp_instance_regwrite N 'HKEY _ CURRENT_USER ', n' Software \ Microsoft \ MSSQLServer', n' LastBackupFileDir ', REG_SZ, n' D: \ Program Files \ Microsoft SQL Server \ MSSQL $ FANHUI \ BACKUP \'
Rewrite backup path

Sp_rename 'tablename. id1', 'id'
Change Field name