Contents
if db_id('dbname') is not null
if object_id('object_name', 'U') is not null -- for table
if object_id('object_name', 'P') is not null -- for SP
These are much easier to read and use than queries against system tables/views.
使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息 语法：SET NOCOUNT { ON | OFF } 默认情况下为 OFF
即使当 SET NOCOUNT 为 ON 时，也更新 @@ROWCOUNT 函数。
当 SET NOCOUNT 为 ON 时，将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft® SQL Server™ 提供的实用工具执行查询时，在 Transact-SQL 语句（如 SELECT、INSERT、UPDATE 和 DELETE）结束时将不会在查询结果中显示“nn rows affected”。
List of all MSSQL INFORMATION_SCHEMA views:
CHECK_CONSTRAINTS Holds information about constraints in the database
COLUMN_DOMAIN_USAGE Identifies which columns in which tables are user-defined datatypes
COLUMN_PRIVILEGES Has one row for each column level permission granted to or by the current user
COLUMNS Lists one row for each column in each table or view in the database
CONSTRAINT_COLUMN_USAGE Lists one row for each column that has a constraint defined on it
CONSTRAINT_TABLE_USAGE Lists one row for each table that has a constraint defined on it
DOMAIN_CONSTRAINTS Lists the user-defined datatypes that have rules bound to them
DOMAINS Lists the user-defined datatypes
KEY_COLUMN_USAGE Lists one row for each column that's defined as a key
PARAMETERS Lists one row for each parameter in a stored procedure or user-defined function
REFERENTIAL_CONSTRAINTS Lists one row for each foreign constraint
ROUTINES Lists one row for each stored procedure or user-defined function
ROUTINE_COLUMNS Contains one row for each column returned by any table-valued functions
SCHEMATA Contains one row for each database
TABLE_CONSTRAINTS Lists one row for each constraint defined in the current database
TABLE_PRIVILEGES Has one row for each table level permission granted to or by the current user
TABLES Lists one row for each table or view in the current database
VIEW_COLUMN_USAGE Lists one row for each column in a view including the base table of the column where possible
VIEW_TABLE_USAGE Lists one row for each table used in a view
VIEWS Lists one row for each view
USE EVENT_SERVICE
SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'registrations'
Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object.
Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
USE [event_service];
GO
BEGIN TRANSACTION
IF NOT EXISTS(SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'registrations' AND COLUMN_NAME = 'fundraiser_id')
ALTER TABLE [dbo].[registrations]
ADD fundraiser_id BIGINT
COMMIT TRANSACTION
GO
USE [form_service]
BEGIN TRANSACTION
IF EXISTS ( SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID('form_entries')
AND name = 'label' )
ALTER TABLE [dbo].[form_entries]
ALTER COLUMN [label] NVARCHAR(600) NULL
COMMIT TRANSACTION
select o.*, rs.RunningSum, rs.SameCode
from Order o
cross apply
(
select
sum(Amount) as RunningSum,
sum(case when p.OrderCode = o.OrderCode then Amount else 0 end) as SameCode
from Order P
where P.OrderDate <= O.OrderDate
) rs