Comments
Sort by recent activity
1. Run this query, save results to excel and then send to the business analyst to fill in the description
SELECT TABLE_SCHEMA AS SchemaName,
TABLE_NAME AS TableName,
COLUMN_NAME AS ColumnName,
'' AS [Description goes here]
FROM
INFORMATION_SCHEMA.COLUMNS
ORDER BY SchemaName,
TableName,
ColumnName;<br>
2. Once you have the description from the BA, plug them into this query exec sp_addextendedproperty
@name = N'MS_Description'
,@value = N'Description supplied by the business analyst'
,@level0type = N'Schema', @level0name = 'dbo'
,@level1type = N'Table', @level1name = 'The table name goes here'
,@level2type = N'Column', @level2name = 'The column name goes here'
3. Run SQL doc and it should produce what you need This all said, I've not actually tried it myself yet. / comments
1. Run this query, save results to excel and then send to the business analyst to fill in the description
SELECT TABLE_SCHEMA AS SchemaName,
TABLE_NAME AS TableName,
COLUMN_NAME AS C...
Hello the below link explains how you can use sp_addextendedproperty within SQL server to add explanations to column and tables and store this information in the database... You can then use red-gate SQL doc to use that information to generate a data dictionary in Word/PDF/HTML https://www.red-gate.com/hub/product-learning/sql-doc/documenting-a-sql-server-data-catalog-in-html-and-git-markdown?product=sql-doc One potential alternative tool is Microsoft Purview - it should mean that you can add table/column descriptions using a web UI (as asset descriptions within Purview Data Catalog)... although I have not actually used Purview yet so remains unproven to me as of today. cheers Ben / comments
Hellothe below link explains how you can use sp_addextendedproperty within SQL server to add explanations to column and tables and store this information in the database... You can then use red-gat...
as an update, in case someone has similar use case, I am now looking at two options: 1. 'analyse data' feature in excel 2. 'column profiling' feature in power bi. struggling to get the Power BI to work but will do some more testing. more information below: https://support.microsoft.com/en-us/office/analyze-data-in-excel-3223aab8-f543-4fda-85ed-76bb0295ffc4 https://sqlskull.com/2021/02/28/profile-data-in-power-bi/ / comments
as an update, in case someone has similar use case, I am now looking at two options:1. 'analyse data' feature in excel2. 'column profiling' feature in power bi.struggling to get the Power BI to w...
OK, thanks @Ben_P - useful to know [image] / comments
OK, thanks @Ben_P - useful to know
I can share something I have done at three companies using Sql Source Control that has worked brilliantly but probably only works if there are one or two developers co-located. At the time we used SVN and TFVC and didn't use branching. Also, I was pretty strict on one-piece flow. It's not best practice but is so quick and easy to setup and move code around environments. 1. Hook all your environments up to source control using the 'dedicated' model irrespective of whether you are using shared or dedicated environments. for me, this included prod. 2. Develop in your development environment and commit using Sql Source Control 3. When you want to move the change to a test environment, just use the Get-Latest tab. Same with production deployments. 4. If you ever did need to do an emergency hot-fix in prod. Just push to source-control from there and make sure you get the latest on your development environment straight away. Some people may baulk at this haha but I reckon I have done over 3,000 commits and 100's of production deployments using this method without ever running into a problem!! It also means you only ever work in one tool, SSMS. CI/CD pipelines are all the rage but imo they are not trivial to setup and they are not essential to building great software, especially for very small, tight-knit teams. Also worth noting that this was on Data Warehouse projects and I had created automated tests so I knew early whether my changes would work - the tests just weren't triggered, i had to click a button [image] / comments
I can share something I have done at three companies using Sql Source Control that has worked brilliantly but probably only works if there are one or two developers co-located. At the time we use...
It has bailed out a lot of teams I have worked in whose database development process could only be described as a free-for-all. / comments
It has bailed out a lot of teams I have worked in whose database development process could only be described as a free-for-all.
hi @SkipSailors - if you have SQL Server 2016 SP1 or onwards you can use the CREATE OR ALTER PROCEDURE statement. example below: CREATE OR ALTER PROCEDURE dbo.sp_test AS SELECT 1; GO EXEC dbo.sp_test; go CREATE OR ALTER PROCEDURE dbo.sp_test AS SELECT 2 go EXEC dbo.sp_test / comments
hi @SkipSailors - if you have SQL Server 2016 SP1 or onwards you can use the CREATE OR ALTER PROCEDURE statement.example below:CREATE OR ALTER PROCEDURE dbo.sp_testASSELECT 1;GOEXEC dbo.sp_test;goC...
Hi @gmartin Welcome to the forums. We had this scenario too. As well as the options listed above, here are some other possible options. 1. Create SSIS packages that will copy the data over from prod to dev. This will be a data only refresh. (You can use BIML to make it dynamic) 2. Backup/Restore from prod every night but also schedule a job to publish/deploy your dev work from source control to the freshly restored copy on your dev server 3. Create a separate dev database and do your development work using cross database references until they are ready for production. (not great but can work) 4. Truncate/Insert Into using linked server 5. Truncate/Insert Into using a replicated copy of the production database. 6. Truncate/Insert Into using polybase 7. Use transactional replication (prod=publisher, dev=subscriber). You can still do your dev work on the replicated copy. Out of all of these, 7 would prob be the simplest. Needless to say, pros and cons to all of them. We went for option 1. We had a really smart developer come in who automated it all for us. "Also, I see things on the Red Gate site about DB Dev Ops. To be honest, I don't exactly know what that term precisely means. Any pointers toward educating me on that would also be appreciated." As for this, I have written a post to help people get started with devops: https://benbrown-sql.com/devops/ Best of luck Ben / comments
Hi @gmartin Welcome to the forums. We had this scenario too.As well as the options listed above, here are some other possible options.1. Create SSIS packages that will copy the data over from pro...
I'm starting a new job at the end of Feb - In the new role I want to focus on helping create a 'high performing team'. on the technical side, one thing I will probably do is to demo a few different products and get the team to decide upon their DevOps toolchain as a group. / comments
I'm starting a new job at the end of Feb - In the new role I want to focus on helping create a 'high performing team'. on the technical side, one thing I will probably do is to demo a few differen...
SQL Source Control just edges it over Prompt - sometimes getting database developers to use source control is like herding cats. SQL Source control makes it so easy that you see very little resistance and the devs can concentrate on delivering value. / comments
SQL Source Control just edges it over Prompt - sometimes getting database developers to use source control is like herding cats. SQL Source control makes it so easy that you see very little resist...