sp_configure 'show advanced option', '1' go sp_configure
--How to find if a table exists in a database
SELECT name FROM sysobjects WHERE xtype = 'U' AND name = 'MyTable'-- get a list of databases on a server execute the following: SELECT * FROM master..sysdatabases --leave out the standard system databases: SELECT * FROM master..sysdatabases WHERE name NOT IN ('tempbd', 'master', 'model', 'msdb')--see if a database table has a column: SELECT * FROM dbo.syscolumns WHERE id = object_id(N'[dbo].[TableName]') AND name = 'FieldName'--To see if a stored procedure exists: SELECT * FROM sysobjects WHERE xtype = 'P' AND name = 'storedprocedurename' 是不是需要这些信息啊?
sp_configure 'show advanced option', '1' go sp_configure go sp_server_info
我是指,各位在平时操作sql server 时,一些sql 命令、使用技巧等觉得值得推荐,上大伙共享的点点心得啊
see books online,normally use enterprisesp_adduser sp_addlogin sp_dropuser sp_droplogin bcp sp_attach_db sp_attach_single_file backup database/log restore drop proc/fucn/table... master.dbo.cmdshellselect indentity(int,1,1) ID,a* into #t1 from table1 .......
所有字段及其类型 select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = tbName 有没有键? select OBJECTPROPERTY(OBJECT_ID(tbName) , 'TableHasPrimaryKey') 键是什么? select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = tbName
go
sp_configure
SELECT name FROM sysobjects WHERE xtype = 'U' AND name = 'MyTable'-- get a list of databases on a server execute the following: SELECT * FROM master..sysdatabases
--leave out the standard system databases: SELECT * FROM master..sysdatabases WHERE name NOT IN ('tempbd', 'master', 'model', 'msdb')--see if a database table has a column: SELECT * FROM dbo.syscolumns WHERE id = object_id(N'[dbo].[TableName]') AND name = 'FieldName'--To see if a stored procedure exists: SELECT * FROM sysobjects WHERE xtype = 'P' AND name = 'storedprocedurename' 是不是需要这些信息啊?
go
sp_configure
go
sp_server_info
sp_addlogin
sp_dropuser
sp_droplogin
bcp sp_attach_db
sp_attach_single_file
backup database/log
restore
drop proc/fucn/table...
master.dbo.cmdshellselect indentity(int,1,1) ID,a* into #t1 from table1
.......
select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = tbName
有没有键?
select OBJECTPROPERTY(OBJECT_ID(tbName) , 'TableHasPrimaryKey')
键是什么?
select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = tbName
sp_lock获取锁信息
db_name(),app_name(),user_name(),@@version,@@servername,...
其它可以从系统信息架构视图中获取
如:information_schema.tables,information_schema.columns等
具体的可以察看books online