yuxuac的专栏

select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table, '>-' as rel, schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table, substring(column_names, 1, len(column_names)-1) as [fk_columns], fk.name as fk_c.

1. 生成脚本SELECT 'select '''+TABLE_SCHEMA + '.[' + TABLE_NAME + ']'' as TblName, count(1) from ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] union' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TA

select *from [YourDBName].information_schema.columnsorder by table_name, ordinal_position

FYI.declare @count intdeclare @querynvarchar(max)set @query = 'select@count = COUNT(*) from LoadTest2010.dbo.LoadTestReport' EXEC sp_executesql@query, N'@count int OUTP

如果需要比较两个数据库的结构，可以参考以下脚本：使用时请将其中的YourSourceDB及YourTargetDB替换成需要比较的两个DB。DECLARE @Sourcedb sysname DECLARE @Destdb sysname DECLARE @Tablename sysname DECLARE @SQL varchar(max) SELECT @Sourcedb

EXEC sp_changedbowner 'sa';

http://www.cnblogs.com/coy/archive/2013/01/17/2860753.html

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36,

-- 取得节点的值select q.XmlContent.value('(//Root/Items/Item/_Name)[1]','nvarchar(max)') AS ColumnName FROM [dbo].[TableName] q-- 取得节点select q.XmlContent.query('(//Root/Items/Item/_Name)') AS ColumnName...

1. 使用脚本select 'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name + ' PRIMARY KEY...

-- 假设Description是一个长文本SELECT ID, CAST('<![CDATA[' + Description + ']]>' AS XML) FROM YourTable 

 http://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state 执行以下语句，可恢复数据库状态：RESTORE DATABASE <DBName> WITH RECOVERY     ...

-------------------------------SELECT M.ModelID, M.Model, C.Content FROM Models M LEFT JOIN Models_Content C ON C.ContentID = (SELECT MIN(ContentID) FROM Models_Content WHERE ModelID...

https://github.com/cjlee/northwind

EXEC sp_fkeys 'TableName'

declare @TableName sysname = 'Claim'declare @Result varchar(max) = 'public class ' + @TableName + '{'select @Result = @Result + ' public ' + ColumnType + NullableSign + ' ' + ColumnName + ' {

ALTER TABLE [table_name] ALTER COLUMN [column_name] varchar(150)

Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to keep. Then, just delete everything that didn't have a row id:DELETE MyTable FROM MyTableLEFT OUTER

--Copy data from column to columnUPDATE [dbo].[CZHJOriginalData] set [OEMIdentified] = ##table1.[VehiclePartOemID]from [CZHJOriginalData] CODjoin ##table1 on ##table1.ID = COD.IDwhere ##ta

DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])-- for exampleDBCC CHECKIDENT('product', RESEED, 0)

SET IDENTITY_INSERT [dbo].[tblName] ONinsert into [dbo].[tblName]([column1],[column2],[column3]) values(10001,101,104)insert into [dbo].[tblName] ([column1],[column2],[column3]) values(20001,101,141

patindex()Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.函数用于取得符

update TableAset TableA.Column1 = TableB.Column1from TableAjoin TableBon TableA.ID = TableB.IDwhere TableA.IsMostRecent = 1

Oracle-- 导入数据imp system/password@dbName file=D:\backupfile.dmp log=filename.log full=y buffer=30720-- 如果table space 满了，以下命令添加新的dbf文件给现有table spacealter tablespace users add datafile 'D:/app/user

http://www.cnblogs.com/simbaliu/archive/2011/03/11/1981770.html

SELECT parent.ColumnA, (     SELECT child.ColumnB + ','    FROM [YourTable] child    WHERE child.ColumnA = parent.ColumnA     FOR XML PATH('') ) AS 'CombinedColumnB'FROM [YourTable] pare

http://www.cnblogs.com/tylerdonet/archive/2011/05/26/2058980.html

中位值：在每一个group中，值从小到大排列，中间那个值；或者中间那个值及后面的值的平均值。如下表：VehicleModelIDNewCarAmount1200130014001500250026002700;WITH

假设我们要在本地SQL Server Management Studio中访问远程服务器上的数据库，应该怎么做？一个好的方法是使用Linked Server，它可以让我们跨服务器访问数据库，使用的时候就像所有的数据库在一台机器上一样。当然访问速度会比本地访问慢，这取决于网络链接的速度。假设我们有一台远程数据库服务器：MyDBServer，其上有一个数据库用户：sa2，其密码为123456，...

转自：https://www.codeproject.com/articles/33052/visual-representation-of-sql-joins

http://www.sqlusa.com/bestpractices2008/top-group-by/