expressraider: sql server

Mostrando entradas con la etiqueta sql server. Mostrar todas las entradas

Crear nuevo usuario de sql server

En el servidor de base de datos, en la carpeta Security / Logins, agregar el usuario:

En User Mapping, marcar la base de datos a la que se quiera acceder, y marcar db_datareader:

Agregar el usuario:

En Membership, darle el rol db_datareader

Listar todas las tablas que tengan un campo dado

Ej, buscar todos los campos cuyo nombre contenga la palabra nota:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%nota%'
ORDER BY COLUMN_NAME

Migrar un diagrama de base de datos a otra base

/*
SrsScriptDiagram

Adapted from script found online at http://www.conceptdevelopment.net/Database/ScriptDiagram2008/

This script will extract the database diagram @DiagramName from <Database Name> and build
a script that can be used to load that diagram into another copy of the database.

Instructions:
1. Open this script in SQL Server Management Studio
2. Use the Specify values for Template Parameters (Ctrl+Shift+M) to set parameter values:
2a. Replace <Database Name,,> with the source database name.
2b. Replace <Diagram Name,,MainDiagram> with the desired diagram name.
3. Change the value of @TargetDatabase to the target database name, or leave as db_name() to use the current database.
4. Execute the script to build a diagram load script.
5. Save the output script.
6. Run the output script on a copy of the target database to restore the diagram.

Modification History:
Downloaded script from http://www.conceptdevelopment.net/Database/ScriptDiagram2008/
Formatting cleaned up to bring it closer to our standards.
Diagram name creation was set to original name if possible, date/time suffix is only used for uniqueness if needed.
Added explicit Target Database variable
Refactored FOR Template Parameters
*/

USE CFE

GO

DECLARE @DiagramName varchar(128)
, @TargetDatabase nvarchar(255)

SELECT @DiagramName = 'DiagramaCompleto'
, @TargetDatabase = db_name()

DECLARE @diagram_id int
, @index int
, @size int
, @chunk int
, @line varchar(max)
, @eol varchar(10)
, @CurrentDateString nvarchar(100)

-- Set start index, and chunk 'constant' value
SELECT @index = 1 --
, @chunk = 32 -- values that work: 2, 6
-- values that fail: 15,16, 64
, @eol = char(13) + char(10)
, @CurrentDateString = convert ( varchar(60) , sysdatetimeoffset() , 121 )

-- Get PK diagram_id using the diagram's name (which is what the user is familiar with)
SELECT @diagram_id = diagram_id
, @size = DATALENGTH(definition)
FROM sysdiagrams sd
WHERE sd.[name] = @DiagramName

IF ( @diagram_id IS NULL )
BEGIN
print '/**<error>' + @eol + @eol + 'Diagram name [' + @DiagramName + '] could not be found.' + @eol + @eol + '</error>*/'
END
ELSE -- Diagram exists
BEGIN
-- Now with the diagram_id, do all the work
print '/**'
print '<summary>'
print 'Restore diagram ''' + @DiagramName + ''''
print '</summary>'
print '<remarks>'
print 'Generated by SrsScriptDiagram Script'
print 'Will attempt to create [sysdiagrams] table if it doesn''t already exist'
print '</remarks>'
print '<generated>' + @CurrentDateString + '</generated>'
print '*/'
print ''
print ''
print 'USE ' + @TargetDatabase
print ''
print 'print ''=== Tool_ScriptDiagram2008 restore diagram [' + @DiagramName + '] ==='''
print ' -- If the sysdiagrams table has not been created in this database, create it!

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''sysdiagrams'')
BEGIN
-- Create table script generated by Sql Server Management Studio
-- _Assume_ this is roughly equivalent to what Sql Server/Management Studio
-- creates the first time you add a diagram to a 2008 database
CREATE TABLE [dbo].[sysdiagrams]
(
[name] [sysname] NOT NULL,
[principal_id] [int] NOT NULL,
[diagram_id] [int] IDENTITY(1,1) NOT NULL,
[version] [int] NULL,
[definition] [varbinary](max) NULL,
PRIMARY KEY CLUSTERED ( [diagram_id] ASC ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ,
CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED ( [principal_id] ASC , [name] ASC ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
)

EXEC sys.sp_addextendedproperty @name=N''microsoft_database_tools_support'', @value=1 , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N''sysdiagrams''

print ''[sysdiagrams] table was created as it did not already exist''
END

-- Target table will now exist, if it didn''t before'
print 'SET NOCOUNT ON -- Hide (1 row affected) messages'
print 'DECLARE @newid INT'
print 'DECLARE @DiagramSuffix varchar (50)'
print 'DECLARE @NewDiagramName nvarchar(100)' -- asw 2010/03/25 use default name if diagram does not already exist.
print ''
print 'SET @DiagramSuffix = '' '' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)'
print ''
print 'IF EXISTS ( SELECT * FROM sysdiagrams sd WHERE ( sd.name = ''' + @DiagramName + ''' ) )'
print 'BEGIN'
print ' print ''Suffix diagram name with date, to ensure uniqueness.'''
print ' SELECT @NewDiagramName = ''' + @DiagramName + ''' + '' '' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)'
print 'END'
print 'ELSE'
print 'BEGIN'
print ' print ''Use diagram name without suffix.'''
print ' SELECT @NewDiagramName = ''' + @DiagramName + ''''
print 'END'
print ''
print 'print ''Create row for new diagram'''

-- Output the INSERT that _creates_ the diagram record, with a non-NULL [definition],
-- important because .WRITE *cannot* be called against a NULL value (in the WHILE loop)
-- so we insert 0x so that .WRITE has 'something' to append to...

print 'BEGIN TRY'
print ' print ''Write diagram ' + @DiagramName + ' as '' + @NewDiagramName + '' into new row (and get [diagram_id])'''

SELECT @line =
' INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])'
+ ' VALUES ( @NewDiagramName , '+ CAST (sd.principal_id AS VARCHAR(100))+', '+CAST (sd.version AS VARCHAR(100))+', 0x)'
FROM sysdiagrams sd
WHERE ( sd.diagram_id = @diagram_id )

print @line
print ' SET @newid = SCOPE_IDENTITY()'
print 'END TRY'
print 'BEGIN CATCH'
print ' print ''XxXxX '' + Error_Message() + '' XxXxX'''
print ' print ''XxXxX END Tool_ScriptDiagram2008 - fix the error before running again XxXxX'''
print ' RETURN'
print 'END CATCH'
print ''
print 'print ''Now add all the binary data...'''
print 'BEGIN TRY'

WHILE ( @index < @size )
BEGIN
-- Output as many UPDATE statements as required to append all the diagram binary
-- data, represented as hexadecimal strings
SELECT @line =
' UPDATE sysdiagrams SET [definition] .Write ('
+ ' ' + UPPER(sys.fn_varbintohexstr (SUBSTRING (sd.definition, @index, @chunk)))
+ ', null, 0) WHERE ( diagram_id = @newid ) -- index:' + CAST(@index AS VARCHAR(100))
FROM sysdiagrams sd
WHERE (sd.diagram_id = @diagram_id )

print @line

SET @index = @index + @chunk

END

print ''
print ' print ''=== Finished writing diagram id '' + CAST(@newid AS VARCHAR(100)) + '' ==='''
print ' print ''=== Refresh your Databases-[DbName]-Database Diagrams to see the new diagram ==='''
print 'END TRY'
print 'BEGIN CATCH'
print ' -- If we got here, the [definition] updates didn''t complete, so delete the diagram row'
print ' -- (and hope it doesn''t fail!)'
print ' DELETE FROM sysdiagrams WHERE diagram_id = @newid'
print ' print ''XxXxX '' + Error_Message() + '' XxXxX'''
print ' print ''XxXxX END Tool_ScriptDiagram2008 - fix the error before running again XxXxX'''
print ' RETURN'
print 'END CATCH'
END

print 'USE master'
print ''

Listar las tablas que no tienen claves foranea (FK)

DECLARE @TablasConFk TABLE
(
[object_Id] int
)

INSERT @TablasConFk ( [object_Id] )
SELECT referenced_object_id
FROM sys.foreign_keys
UNION
SELECT [parent_object_id]
FROM sys.foreign_keys

SELECT name as Tabla
FROM sys.objects obj
LEFT OUTER JOIN @TablasConFk conFk ON ( obj.[object_id] = conFk.[object_Id] )
WHERE type_desc = 'USER_TABLE'
AND conFk.[object_Id] IS NULL
ORDER BY Tabla

Listar comentarios de tablas y campos

Listar comentarios de las tablas

SELECT objname, CONVERT(VARCHAR(500), value)
FROM ::FN_LISTEXTENDEDPROPERTY(N'MS_DESCRIPTION', N'USER', N'DBO', N'TABLE', NULL, NULL, NULL)
where objtype = 'TABLE'

Listar comentarios de los campos de una tabla

select
st.name [Table],
sc.name [Column],
sep.value [Description]
from sys.tables st
inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id
and sc.column_id = sep.minor_id
and sep.name = 'MS_Description'
where st.name = 'Nombre de la Tabla'

and sc.name = 'Nombre del campo'

Con stored procedures...

//crea o actualiza la propiedad MS_description, de la tabla especificada
exec mer_ActualizarDescripcionTabla 'Cliente', 'tabla de clientes de la empresa'-

//actualiza o crea la propiedad especificada, para el campo de la tabla especifcadas, y le pone el valor indicado
exec mer_ActualizarPropiedadCampo 'Tipo', 'Cliente', 'Nombre', 'Nombre del cliente'

//elimina la propiedad MS_description, de la tabla especificada
exec mer_EliminarDescripcionTabla 'Cliente'

//elimina la propiedad que tenga el nombre especificado, del campo de la tabla especificados
exec mer_EliminarPropiedadCampo 'Tipo', 'Cliente', 'Nombre'

//lista los comentarios de todas las tablas
exec mer_listarComentariosTablas

//lista todas las propiedades del tipo especificado, de todos los campos de todas las tablas
exec mer_listarPropiedadesCampos 'Tipo'

//lista el valor de una propiedad dada de un campo de una tabla.
exec mer_listarPropiedadCampo 'Tipo', 'Cliente', 'FechaNacimiento'

Stored procedures

--Comienzo de SPs -------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[mer_ActualizarDescripcionTabla]
-- Los parámetros son el nombre de la tabla y la descripción
@nombre_tabla sysname = '',
@descripcion_tabla sql_variant = ''
AS
BEGIN
BEGIN TRY

IF EXISTS (SELECT OBJNAME AS 'tabla', VALUE AS 'COMENTARIO'
FROM ::FN_LISTEXTENDEDPROPERTY(N'MS_DESCRIPTION', N'USER', N'DBO', N'TABLE',
@nombre_tabla, NULL, NULL) XP
WHERE XP.NAME IN (N'MS_DESCRIPTION'))

EXEC sys.sp_updateextendedproperty
@name=N'MS_Description',
@value=@descripcion_tabla,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla
ELSE
EXEC sys.sp_addextendedproperty
@name=N'MS_Description',
@value=@descripcion_tabla,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

PRINT 'No se pudo agregar la descripción ''' + convert(nvarchar(128),@descripcion_tabla) + '''a la tabla ' + convert(nvarchar(128),@nombre_tabla)
PRINT 'Error Line: ' + convert(varchar(50),ERROR_LINE())
PRINT 'ErrorMessage: ' + ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
END

-------------------------------------------------------------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[mer_ActualizarPropiedadCampo]
-- Los parámetros son el nombre de la tabla, el nombre de la columna y la descripción
@propiedad sysname = '',
@nombre_tabla sysname = '',
@nombre_campo sysname = '',
@descripcion_campo sql_variant = ''
AS
BEGIN TRY
IF EXISTS(SELECT objname, value
FROM fn_listextendedproperty (@propiedad, N'schema', 'dbo',
N'table', @nombre_tabla, N'column', null) WHERE objname = @nombre_campo)
EXEC sys.sp_updateextendedproperty
@name=@propiedad,
@value=@descripcion_campo,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla,
@level2type=N'COLUMN',
@level2name=@nombre_campo
ELSE
EXEC sys.sp_addextendedproperty
@name=@propiedad,
@value=@descripcion_campo,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla,
@level2type=N'COLUMN',
@level2name=@nombre_campo
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

PRINT 'No se pudo agregar la descripción ''' + convert(nvarchar(128),@descripcion_campo) + '''al campo ' + convert(nvarchar(128),@nombre_campo) + ' en la tabla ' + convert(nvarchar(128),@nombre_tabla)
PRINT 'Error Line: ' + convert(varchar(50),ERROR_LINE())
PRINT 'ErrorMessage: ' + ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH

-------------------------------------------------------------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[mer_EliminarDescripcionTabla]
@nombre_tabla sysname = ''
AS
BEGIN TRY
EXEC sys.sp_dropextendedproperty
@name=N'MS_Description',
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

PRINT 'No se pudo agregar la descripción ''' + '''al campo ' + ' en la tabla ' + convert(nvarchar(128),@nombre_tabla)
PRINT 'Error Line: ' + convert(varchar(50),ERROR_LINE())
PRINT 'ErrorMessage: ' + ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH

-------------------------------------------------------------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[mer_EliminarPropiedadCampo]
@propiedad sysname = '',
@nombre_tabla sysname = '',
@nombre_campo sysname = ''
AS
BEGIN TRY
EXEC sys.sp_dropextendedproperty
@name=@propiedad,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla,
@level2type=N'COLUMN',
@level2name=@nombre_campo
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

PRINT 'No se pudo agregar la descripción ''' + '''al campo ' + convert(nvarchar(128),@nombre_campo) + ' en la tabla ' + convert(nvarchar(128),@nombre_tabla)
PRINT 'Error Line: ' + convert(varchar(50),ERROR_LINE())
PRINT 'ErrorMessage: ' + ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH

-------------------------------------------------------------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[mer_ListarComentariosTablas]
AS

DECLARE @comentarios TABLE(tabla sysname, descripcion VARCHAR(500))
INSERT INTO @comentarios (tabla, descripcion)
SELECT objname, CONVERT(VARCHAR(500), value)
FROM ::FN_LISTEXTENDEDPROPERTY(N'MS_DESCRIPTION', N'USER', N'DBO', N'TABLE', NULL, NULL, NULL)
INSERT INTO @comentarios (tabla)
SELECT table_name
FROM information_schema.tables
WHERE table_name NOT IN
(select tabla FROM @comentarios)
AND TABLE_TYPE <> 'VIEW'

SELECT * from @comentarios ORDER BY tabla

-------------------------------------------------------------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[mer_ListarPropiedadesCampos]
@propiedad sysname = ''
AS

DECLARE @propiedades TABLE (
esquema sysname,
tabla sysname,
columna sysname,
valor VARCHAR(500))

DECLARE c CURSOR FOR
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type != 'VIEW'

DECLARE @s sysname, @t sysname

OPEN c
FETCH c INTO @s, @t
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO @propiedades (esquema, tabla, columna, valor)
SELECT @s, @t, objname, convert(varchar(500), value)
FROM fn_listextendedproperty (@propiedad, N'schema', @s, N'table', @t, N'column', null);

FETCH c INTO @s, @t
END
CLOSE c
DEALLOCATE c

INSERT INTO @propiedades (esquema, tabla, columna)
SELECT table_schema, table_name, column_name
FROM information_schema.columns s
WHERE table_name IN
(SELECT table_name
FROM information_schema.tables
where TABLE_TYPE <> 'VIEW')
AND NOT EXISTS
(SELECT *
FROM @propiedades c
WHERE s.table_schema = c.esquema
AND s.table_name = c.tabla
AND s.column_name = c.columna)

SELECT *
FROM @propiedades
ORDER BY esquema, tabla, columna

-------------------------------------------------------------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[mer_ListarPropiedadCampo]
@propiedad sysname = '',
@tabla sysname = '',
@campo sysname = ''
AS

DECLARE @propiedades TABLE (
esquema sysname,
tabla sysname,
columna sysname,
valor VARCHAR(500))

DECLARE c CURSOR FOR
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type != 'VIEW' and
table_name = @tabla

DECLARE @s sysname, @t sysname

OPEN c
FETCH c INTO @s, @t
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO @propiedades (esquema, tabla, columna, valor)
SELECT @s, @t, objname, convert(varchar(500), value)
FROM fn_listextendedproperty (@propiedad, N'schema', @s, N'table', @tabla, N'column', @campo);

FETCH c INTO @s, @t
END
CLOSE c
DEALLOCATE c

select *
from @propiedades

Listar tabla, campo, tipo y tamaño en SQL Server

use [base]

SELECT
so.name AS Tabla,
sc.name AS Columna,
st.name AS Tipo,
sc.max_length AS Tamaño
FROM
sys.objects so INNER JOIN
sys.columns sc ON
so.object_id = sc.object_id INNER JOIN
sys.types st ON
st.system_type_id = sc.system_type_id AND
st.name != 'sysname'
WHERE
so.type = 'U'
ORDER BY
so.name,
sc.name

Completar con ceros un campo (padl, padr, pad, padding) en un query sql

select right('0000'+campo1,4) tx, *
from tabla1

Obtener todas lase vistas de una base de datos

use base

select v.name, s.definition from sys.views v, sys.sql_modules s
where v.object_id=s.object_id

Hacer SELECT de un SELECT

Tener en cuenta que el order by tiene que ir afuera!!!

select *
from
(select campo1
from tabla
group by xxx) tabla1
order by campo1

Listar todos los triggers de una base de datos

SELECT
   ServerName = @@servername,
   DatabaseName = db_name(),
   SchemaName = isnull( s.name, '' ),
   TableName = isnull( o.name, 'DDL Trigger' ),
   TriggerName = t.name,
   Defininion = object_definition( t.object_id )
FROM sys.triggers t
   LEFT JOIN sys.all_objects o
   ON t.parent_id = o.object_id
   LEFT JOIN sys.schemas s
   ON s.schema_id = o.schema_id
ORDER BY
   SchemaName,
   TableName,
   TriggerName

Scripts para crear comentarios en campos y tablas en SQL Server

--Ejemplos de uso:
--exec mer_listarComentariosCampos
--exec mer_listarComentariosTablas
--exec mer_ActualizarDescripcionCampo 'TABLA', 'CAMPO', 'DESCRIPCION'
--exec mer_ActualizarDescripcionTabla 'TABLA', 'DESCRIPCION'
--exec mer_EliminarDescripcionTabla 'TABLA'
--exec mer_EliminarDescripcionCampo 'TABLA', 'CAMPO'

USE [BASE]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mer_ListarComentariosCampos]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[mer_ListarComentariosCampos]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mer_ListarComentariosTablas]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[mer_ListarComentariosTablas]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mer_ActualizarDescripcionTabla]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[mer_ActualizarDescripcionTabla]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mer_ActualizarDescripcionCampo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[mer_ActualizarDescripcionCampo]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mer_EliminarDescripcionTabla]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[mer_EliminarDescripcionTabla]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mer_EliminarDescripcionCampo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[mer_EliminarDescripcionCampo]
GO

CREATE PROCEDURE [dbo].[mer_ListarComentariosCampos]
AS

DECLARE @comentarios TABLE (
   esquema sysname,
   tabla sysname,
   columna sysname,
   descripcion VARCHAR(500))

DECLARE c CURSOR FOR
   SELECT table_schema, table_name
   FROM information_schema.tables

DECLARE @s sysname, @t sysname

OPEN c
FETCH c INTO @s, @t
WHILE @@FETCH_STATUS = 0 BEGIN
   INSERT INTO @comentarios (esquema, tabla, columna, descripcion)
   SELECT @s, @t, objname, convert(varchar(500), value)
   FROM fn_listextendedproperty (N'MS_Description', N'schema', @s, N'table', @t, N'column', null);

   FETCH c INTO @s, @t
END
CLOSE c
DEALLOCATE c

INSERT INTO @comentarios (esquema, tabla, columna)
SELECT table_schema, table_name, column_name
  FROM information_schema.columns s
WHERE NOT EXISTS
   (SELECT *
   FROM @comentarios c
   WHERE s.table_schema = c.esquema
   AND s.table_name = c.tabla
   AND s.column_name = c.columna)

SELECT *
  FROM @comentarios
ORDER BY esquema, tabla, columna

GO

-------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[mer_ListarComentariosTablas]
AS

DECLARE @comentarios TABLE(tabla sysname, descripcion VARCHAR(500))
INSERT INTO @comentarios (tabla, descripcion)
SELECT objname, CONVERT(VARCHAR(500), value)
   FROM ::FN_LISTEXTENDEDPROPERTY(N'MS_DESCRIPTION', N'USER', N'DBO', N'TABLE', NULL, NULL, NULL)
INSERT INTO @comentarios (tabla)
  SELECT table_name
   FROM information_schema.tables
   WHERE table_name NOT IN
   (select tabla FROM @comentarios)

SELECT * from @comentarios ORDER BY tabla

GO

-------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[mer_ActualizarDescripcionTabla]
-- Los parámetros son el nombre de la tabla y la descripción
@nombre_tabla sysname = '',
@descripcion_tabla sql_variant = ''
AS
BEGIN
BEGIN TRY

IF EXISTS (SELECT OBJNAME AS 'tabla', VALUE AS 'COMENTARIO'
FROM ::FN_LISTEXTENDEDPROPERTY(N'MS_DESCRIPTION', N'USER', N'DBO', N'TABLE',
@nombre_tabla, NULL, NULL) XP
WHERE XP.NAME IN (N'MS_DESCRIPTION'))

EXEC sys.sp_updateextendedproperty
@name=N'MS_Description',
@value=@descripcion_tabla,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla
ELSE
EXEC sys.sp_addextendedproperty
@name=N'MS_Description',
@value=@descripcion_tabla,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

PRINT 'No se pudo agregar la descripción ''' + convert(nvarchar(128),@descripcion_tabla) + '''a la tabla ' + convert(nvarchar(128),@nombre_tabla)
PRINT 'Error Line: ' + convert(varchar(50),ERROR_LINE())
PRINT 'ErrorMessage: ' + ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
END
GO
-------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[mer_ActualizarDescripcionCampo]
-- Los parámetros son el nombre de la tabla, el nombre de la columna y la descripción
@nombre_tabla sysname = '',
@nombre_campo sysname = '',
@descripcion_campo sql_variant = ''
AS
BEGIN TRY
IF EXISTS(SELECT objname, value
   FROM fn_listextendedproperty (N'MS_Description', N'schema', 'dbo',
N'table', @nombre_tabla, N'column', null) WHERE objname = @nombre_campo)
EXEC sys.sp_updateextendedproperty
@name=N'MS_Description',
@value=@descripcion_campo,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla,
@level2type=N'COLUMN',
@level2name=@nombre_campo
ELSE
EXEC sys.sp_addextendedproperty
@name=N'MS_Description',
@value=@descripcion_campo,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla,
@level2type=N'COLUMN',
@level2name=@nombre_campo
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

PRINT 'No se pudo agregar la descripción ''' + convert(nvarchar(128),@descripcion_campo) + '''al campo ' + convert(nvarchar(128),@nombre_campo) + ' en la tabla ' + convert(nvarchar(128),@nombre_tabla)
PRINT 'Error Line: ' + convert(varchar(50),ERROR_LINE())
PRINT 'ErrorMessage: ' + ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
GO
-------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[mer_EliminarDescripcionTabla]
@nombre_tabla sysname = ''
AS
BEGIN TRY
EXEC sys.sp_dropextendedproperty
@name=N'MS_Description',
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

PRINT 'No se pudo agregar la descripción ''' + '''al campo ' + ' en la tabla ' + convert(nvarchar(128),@nombre_tabla)
PRINT 'Error Line: ' + convert(varchar(50),ERROR_LINE())
PRINT 'ErrorMessage: ' + ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
GO
-------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[mer_EliminarDescripcionCampo]
@nombre_tabla sysname = '',
@nombre_campo sysname = ''
AS
BEGIN TRY
EXEC sys.sp_dropextendedproperty
@name=N'MS_Description',
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@nombre_tabla,
@level2type=N'COLUMN',
@level2name=@nombre_campo
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

PRINT 'No se pudo agregar la descripción ''' + '''al campo ' + convert(nvarchar(128),@nombre_campo) + ' en la tabla ' + convert(nvarchar(128),@nombre_tabla)
PRINT 'Error Line: ' + convert(varchar(50),ERROR_LINE())
PRINT 'ErrorMessage: ' + ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
GO
-------------------------------------------------------------------------------------------------------------

Exportar e importar datos con BCP

bcp "select * from base..tabla"
queryout c:\temp\out1.dat  -S 
servidor\instancia -T -n -U usuario -P password


Para exportar:
bcp PEPE out C:\pepe.csv -S Xsrv\sql2005 -d prueba -T -c
importar:
bcp PEPE2 in C:\pepe.csv -S Xsrv\sql2005 -d prueba -T -c

Listar todos los stored procedures de una base

SELECT     sysobjects.name, syscomments.text
FROM         sysobjects INNER JOIN
                      syscomments ON sysobjects.id = syscomments.id
WHERE    
(sysobjects.type = 'P')

Exportar una tabla de SQL a Excel

Para obtener el string de conexión, ver este post

Sub ExportarSQL_A_Excel(sProducto, sPeriodo)
 Set conn = CreateObject("ADODB.Connection") 

 sNombreArch = "C:\temp\archivo.xlsx"
 
 conn.open "Provider=SQLNCLI.1;Persist Security Info=False;User ID=usuario;Password=password;Initial Catalog=nombreBase;Data Source=servidor\instancia"
  
 Set objCommand = CreateObject("ADODB.Command")
 Set objCommand.ActiveConnection = conn
 objCommand.CommandTimeout = 6000
 
  
 objCommand.CommandText = "SELECT * FROM tabla where condicion"
 
 Set rs = CreateObject("ADODB.recordset")
 Set rs = objCommand.Execute()
 
 
 Dim e
 Dim wb
 Dim sheet
 Dim sNombreArch
  
 Set e = CreateObject("Excel.Application")
  
 e.Workbooks.Add
 Set wb = e.ActiveWorkbook 
 Set sheet = wb.ActiveSheet
 
 nCantColumnas = rs.fields.count
 
 'Agrego los nombres de columnas
 For i = 0 to nCantColumnas - 1
  sheet.Range(Chr(64 + i + 1) & 1) = rs.fields(i).name
 Next
 
 sheet.Range("A2").CopyFromRecordset rs
  
 Set sheet = Nothing
 wb.SaveAs sNombreArch
 wb.Close
 Set wb = Nothing
 
 e.Quit
 Set e = Nothing
End Sub

Conectarse a SQLServer desde vbscript

Una forma sencilla de crear el string de conexión es utilizando este tip: http://expressraider.blogspot.com/2009/12/crear-conexion-dsnless.html

Set conn = CreateObject("ADODB.Connection")
wscript.timeout = 0
conn.open "Provider=SQLNCLI.1;Persist Security Info=False;User ID=usr1;Password=pwd1;Initial Catalog=nombreBase;Data Source=nombreServidor\nombreInstancia"


Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = conn
objCommand.CommandTimeout = 120000

objCommand.CommandText = "SELECT * FROM tabla"

Set rs = CreateObject("ADODB.recordset")
Set rs = objCommand.Execute()